
use [KorleBu_STA_Inventory2]

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'TestAddItem')

DROP PROCEDURE TestAddItem

GO

CREATE PROCEDURE [dbo].[TestAddItem] (
	@itemID INT
)

AS
BEGIN
--DEMONSTRATION OF CURSORS
DECLARE @SubID int
DECLARE @StoreUnitID int
DECLARE @StoreID int

SET @StoreID = (SELECT IC.StoreID FROM Items I JOIN ItemCategories IC ON I.ItemCategoryID = IC.ItemCategoryID WHERE I.ItemID = @itemID)

DECLARE SubList CURSOR
FOR
--CAN ONLY PROCES SELECT STATEMENTS
SELECT SubUnitID FROM SubUnits WHERE StatusID = 70

--OPEN CURSOR

OPEN SubList
FETCH NEXT FROM SubList
INTO @SubID

WHILE @@FETCH_STATUS = 0
BEGIN
	IF EXISTS(SELECT StoreUnitID FROM StoreUnits WHERE StoreID = @StoreID AND SubUnitID = @SubID)
	BEGIN
		SET @StoreUnitID = (SELECT StoreUnitID FROM StoreUnits WHERE StoreID = @StoreID AND SubUnitID = @SubID)
		INSERT INTO StoreUnitItems (ItemID, StoreUnitID, StoreUnitQty)
		VALUES (@itemID, @StoreUnitID, 0)
	END
FETCH NEXT FROM SubList

INTO @SubID

END

--DON"T FORGET TO CLOSE
CLOSE SubList
--DELALLOCATE
DEALLOCATE SubList
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'spModifyRequsition')

DROP PROCEDURE spModifyRequsition

GO

--Created By Benjamin Strickler 7/31/2012
CREATE PROCEDURE [dbo].[spModifyRequsition] (
	@requisitionID	int,
	@itemID			int,
	@qty			int
)
AS
BEGIN
DECLARE @results NVARCHAR(100)
	
	UPDATE RequisitionItems
	SET RequisitionQty = @qty
	WHERE
		RequisitionID = @requisitionID
		AND ItemID = @itemID
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'spModifyRequest')

DROP PROCEDURE spModifyRequest

GO

--Created By Benjamin Strickler 7/31/2012
CREATE PROCEDURE [dbo].[spModifyRequest] (
	@requestID		int,
	@itemID			int,
	@qty			int
)
AS
BEGIN
DECLARE @results NVARCHAR(100)
	
	UPDATE StoreRequestItems
	SET qtyRequested = @qty
	WHERE
		SatelliteStoreRequestID = @requestID
		AND ItemID = @itemID
		

END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'spCreateItemAdjustmentReason')

DROP PROCEDURE spCreateItemAdjustmentReason

GO

--Created By Benjamin Strickler 7/31/2012
CREATE PROCEDURE [dbo].[spCreateItemAdjustmentReason] (
	@adjReason nvarchar(100)
)
AS
BEGIN
DECLARE @results NVARCHAR(100)
	
	IF NOT EXISTS (
		SELECT *
		FROM AdjReasons
		WHERE
			adjName = @adjReason
	)
	BEGIN
		INSERT INTO AdjReasons (adjName)
		VALUES	(@adjReason)
		
		SET @results = NULL
		SELECT @results		
	END
	ELSE
	BEGIN
		SET @results = 'Reason Already Exists'
		SELECT @results
	END
		

END

GO
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_modifyItemAdjustmentReason')

DROP PROCEDURE sp_modifyItemAdjustmentReason

GO

--Created By Jason Vance 27 Nov 2012
CREATE PROCEDURE [dbo].sp_modifyItemAdjustmentReason (
	@adjustID int,
	@newAdjustName nvarchar(100)
)
AS
BEGIN
DECLARE @results NVARCHAR(100)
	
	IF EXISTS (
		SELECT *
		FROM AdjReasons
		WHERE
			adjReasonID = @adjustID
	)
	BEGIN
		UPDATE AdjReasons
		SET
			AdjName = @newAdjustName
		WHERE
			AdjReasonID = @adjustID
			
		SET @results = NULL
		SELECT @results		
	END
	ELSE
	BEGIN
		SET @results = 'Reason Does Not Exist'
		SELECT @results
	END
		

END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_updateSRA')

DROP PROCEDURE sp_updateSRA

GO

----Created by Preston Berrett June 26, 2012
--CREATE PROCEDURE [dbo].[sp_updateSRA]
--	@RequisitionID INT,
--	@SRANo INT,
--	@SRA VARCHAR(100)
--AS
--BEGIN
--DECLARE @results NVARCHAR(100)
--	IF NOT EXISTS (
--		SELECT
--			R.RequisitionID
--		FROM
--			Requisitions R
--		WHERE
--			R.RequisitionID = @RequisitionID
--	)
--	BEGIN --if there aren't any pending requisitions
--		SET @results = 'FALSE';
		
--		SELECT
--			@results;
--	END
--	ELSE
--	BEGIN
		
--		IF (@SRANo = 1)
--			BEGIN
--				UPDATE Requisitions 
--				SET SRA1 = @SRA
--				WHERE
--					RequisitionID = @RequisitionID
--			END
		
--		IF (@SRANo = 2)
--			BEGIN
--				UPDATE Requisitions 
--				SET SRA2 = @SRA
--				WHERE
--					RequisitionID = @RequisitionID
--			END
			
		
--		IF (@SRANo = 3) 
--			BEGIN
--				UPDATE Requisitions 
--				SET SRA3 = @SRA
--				WHERE
--					RequisitionID = @RequisitionID
--			END
			
			
--		IF (@SRANo = 4) 
--			BEGIN
--				UPDATE Requisitions 
--				SET SRA4 = @SRA
--				WHERE
--					RequisitionID = @RequisitionID
--			END
			
			
--		SET @results = 'TRUE';
		
--		SELECT
--			@results;
		
--	END
--END

--GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_updatePassword')

DROP PROCEDURE sp_updatePassword

GO

--created by michael meyer june 7 2012
CREATE PROCEDURE [dbo].[sp_updatePassword] (
	@userID INT,
	@oldPassword NVARCHAR(100),
	@newPassword NVARCHAR(100),
	@salt NVARCHAR(MAX)
)
AS
BEGIN

	DECLARE @results NVARCHAR(100)
	DECLARE @completed BIT

	SET @results = 'Unknown Error'
	SET @completed = 0

	IF NOT EXISTS (
		SELECT
			U.userID
		FROM
			Users U
		WHERE
			U.userID = @userID
			AND U.pass = @oldPassword
	)
	BEGIN --if the user doesn't exist
		SET @results = 'No user exists by that user name and password.'
		SET @completed = 0
	END
	ELSE --if the user exists
	BEGIN
		SET @results = NULL
		
		UPDATE Users
			SET pass = @newPassword,
				Salt = @Salt
		WHERE
			userID = @userID
			AND pass = @oldPassword
			
		SET @completed = 1
	END
	
	SELECT
		@results
		
	SELECT
		@completed	
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_updateLPO')

DROP PROCEDURE sp_updateLPO

GO

----Created by Michael Meyer
--CREATE PROCEDURE [dbo].[sp_updateLPO] (
--	@requisitionID INT,
--	@LPO NVARCHAR(100)
--)
--AS
--BEGIN
--	DECLARE @results NVARCHAR(100)
--	DECLARE @completed BIT
		
--	IF NOT EXISTS (
--		SELECT
--			R.requisitionID
--		FROM
--			Requisitions R
--		WHERE
--			R.requisitionID = @requisitionID
--	)
--	BEGIN
--		SET @completed = 0
--		SET @results = 'Not a valid requisition.'
--	END
	
--	IF(@completed != 0)
--	BEGIN
--		UPDATE Requisitions
--			SET LPO = @LPO
--		WHERE
--			requisitionID = @requisitionID
		
--		SET @completed = 1
--	END
	
--	SELECT
--		@results [errorMessage]
		
--	SELECT
--		@completed
	
--END

--GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_updateLoginHistory')

DROP PROCEDURE sp_updateLoginHistory

GO

CREATE PROCEDURE [dbo].[sp_updateLoginHistory](
	@username NVARCHAR(255),
	@password NVARCHAR(255)	
)
AS 
BEGIN
	UPDATE LoginHistory
		SET EndDate = GETDATE()
	FROM
		Users U
		INNER JOIN LoginHistory LH ON LH.userID = U.userID
	WHERE
		U.Username = @username
		AND U.Pass = @password
		AND LH.EndDate IS NULL
END
	
GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_updateItemQuantityReceived')

DROP PROCEDURE sp_updateItemQuantityReceived

GO

--Created by Preston Berrett July 26, 2012
CREATE PROCEDURE [dbo].[sp_updateItemQuantityReceived]
	@RequisitionID INT,
	@ItemID INT,
	@Quantity INT,
	@LPO INT,
	@VendorID INT,
	@ReceivingOfficer VARCHAR(100)
AS
BEGIN
DECLARE @requisitionItemID INT
DECLARE @completed BIT
	SET @completed = 0
	
DECLARE @results NVARCHAR(100)
	IF NOT EXISTS (
		SELECT
			RI.RequisitionItemID
		FROM
			RequisitionItems RI
		WHERE
			RI.RequisitionID = @RequisitionID
			AND RI.ItemID = @ItemID	
	)
	BEGIN --if there aren't any store requests
		SET @results = 'No store requests'
		SET @completed = 0
	END
	ELSE	
	BEGIN
		
		SET @requisitionItemID = (
			SELECT
				RI.RequisitionItemID
			FROM
				RequisitionItems RI
			WHERE
				RI.RequisitionID = @RequisitionID
				AND RI.ItemID = @ItemID	
		)
		
			
		INSERT INTO RequisitionReceivedItems (receivedDate, QtyReceived, requisitionItemID, VendorID, ReceivingOfficer)
			VALUES (GETDATE(), @quantity, @requisitionItemID, @VendorID, @ReceivingOfficer)
			
		SET @completed = 1
	END
	
	SELECT
		@results [errorMessages]
	
	SELECT
		@completed

END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_updateEmergencyQty')

DROP PROCEDURE sp_updateEmergencyQty

GO

CREATE PROCEDURE [dbo].[sp_updateEmergencyQty] (
	@ItemID INT,
	@Qty	INT
)

AS
BEGIN

	UPDATE Items
	SET EmergencyQty = @Qty
	WHERE ItemID = @ItemID

END

GO


IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_insertIntoInventory')

DROP PROCEDURE sp_insertIntoInventory

GO

--Created by Preston Berrett July 26, 2012
CREATE PROCEDURE [dbo].[sp_insertIntoInventory]
	@RequisitionID INT,
	@OrderNo NVARCHAR(50),
	@VendorID INT
AS
BEGIN
DECLARE @completed BIT
	SET @completed = 0
	
DECLARE @results NVARCHAR(100) 
	
	BEGIN
		
		DECLARE @RequisitionItemID INT
		DECLARE @getRequisitionItemID CURSOR
		SET @getRequisitionItemID = CURSOR FOR
			SELECT RequisitionItemID FROM RequisitionItems
			WHERE RequisitionID = @RequisitionID
		OPEN @getRequisitionItemID
		FETCH NEXT
		FROM @getRequisitionItemID INTO @RequisitionItemID
		WHILE @@FETCH_STATUS = 0
		BEGIN

			DECLARE @ItemID INT
			DECLARE @quantity INT

			SET @ItemID = (SELECT ItemID FROM RequisitionItems WHERE RequisitionItemID = @RequisitionItemID)
			SET @quantity = (SELECT RequisitionQty FROM RequisitionItems WHERE RequisitionItemID = @RequisitionItemID)
			
			--fix here, VendorCostID is getting assigned to @VendorID
			INSERT INTO Inventory (ItemID, TransactionDate, TransactionQty, VendorCostID, OrderNo)
				VALUES (@ItemID, GETDATE(), @quantity, 
					(SELECT TOP 1 VC.VendorCostID FROM VendorCosts VC WHERE VC.ItemID = @ItemID ORDER BY VC.VendorCost DESC), 
					@OrderNo)

			UPDATE RequisitionItems SET StatusID = 102
				WHERE RequisitionItemID = @RequisitionItemID

			FETCH NEXT
			FROM @getRequisitionItemID INTO @RequisitionItemID
		
		END	
		CLOSE @getRequisitionItemID
		DEALLOCATE @getRequisitionItemID
			
		SET @completed = 1
	END
	
	SELECT
		@results [errorMessages]
	
	SELECT
		@completed

END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_updateEmergencyQty')

DROP PROCEDURE sp_updateEmergencyQty

GO

CREATE PROCEDURE [dbo].[sp_updateEmergencyQty] (
	@ItemID INT,
	@Qty	INT
)

AS
BEGIN

	UPDATE Items
	SET EmergencyQty = @Qty
	WHERE ItemID = @ItemID

END

GO


IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_submitRequisition')

DROP PROCEDURE sp_submitRequisition

GO

--Created by Preston Berrett June 26, 2012
CREATE PROCEDURE [dbo].[sp_submitRequisition]
	@RequisitionID INT
AS
BEGIN
DECLARE @results NVARCHAR(100)
	IF NOT EXISTS (
		SELECT
			R.RequisitionID
		FROM
			Requisitions R
		WHERE
			R.RequisitionID = @RequisitionID	
	)
	BEGIN --if there aren't any pending requisitions
		SET @results = 'No Requisitions';
		
		SELECT
			@results;
	END
	ELSE
	BEGIN
		
		SET @results = NULL;
		
		SELECT
			@results;
			
		UPDATE Requisitions
		SET StatusID = 40
		WHERE RequisitionID = @RequisitionID
	END
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_submitOutsideProcurement')

DROP PROCEDURE sp_submitOutsideProcurement

GO

--Created by Preston Berrett June 26, 2012
CREATE PROCEDURE [dbo].[sp_submitOutsideProcurement]
	@RequisitionID INT,
	@VendorID INT,
	@OrderNo NVARCHAR(50)
AS
BEGIN
DECLARE @results NVARCHAR(100)
	IF NOT EXISTS (
		SELECT
			R.RequisitionID
		FROM
			Requisitions R
		WHERE
			R.RequisitionID = @RequisitionID	
	)
	BEGIN --if there aren't any pending requisitions
		SET @results = 'No Requisitions';
		
		SELECT
			@results;
	END
	ELSE
	BEGIN
		
		SET @results = NULL;
		
		SELECT
			@results;
			
		UPDATE Requisitions
		SET StatusID = 41
		WHERE RequisitionID = @RequisitionID
	END
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_SubmitRequest')

DROP PROCEDURE sp_SubmitRequest

GO

CREATE PROCEDURE [dbo].[sp_SubmitRequest]
	@RequestID		int
AS
BEGIN
	DECLARE @results NVARCHAR(100)
	UPDATE SatelliteStoreRequests
	SET	StatusID = 80
	WHERE SatelliteStoreRequestID = @RequestID
	
	SET @results = 'Request has been submitted';
		
	SELECT @results;
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_reports_itemAdjustmentsByDateRange')

DROP PROCEDURE sp_reports_itemAdjustmentsByDateRange

GO

--Created by Michael Meyer Aug 1, 2012
CREATE PROCEDURE [dbo].[sp_reports_itemAdjustmentsByDateRange] (
	@startDate DATETIME,
	@endDate DATETIME
)
AS
BEGIN
	
	SELECT
		S.storeName,
		U.firstName,
		U.lastName,
		SUI.itemID,
		I.itemName,
		AR.adjName,
		IA.AdjustmentQty [quantity],
		CONVERT(NVARCHAR(100),@startDate) [startDate],
		CONVERT(NVARCHAR(100),@endDate) [endDate]
	FROM
		ItemAdjustments IA
		INNER JOIN adjReasons AR ON AR.AdjReasonID = IA.adjReasonID
		INNER JOIN storeUnitItems SUI ON SUI.storeUnitItemID = IA.ItemID
		INNER JOIN storeUnits SU ON SU.storeUnitID = SUI.storeUnitID
		INNER JOIN stores S ON S.storeID = SU.storeID
		INNER JOIN users U ON U.userID = IA.userID
		INNER JOIN items I ON I.itemID = SUI.itemID
	WHERE
		IA.dateAdjusted BETWEEN @startDate AND @endDate
		
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_reports_currentItemsBySubUnit')

DROP PROCEDURE sp_reports_currentItemsBySubUnit

GO

--Created by Michael Meyer July 18, 2012
CREATE PROCEDURE [dbo].[sp_reports_currentItemsBySubUnit] (
	@subUnitID INT
)
AS
BEGIN

	SELECT
		RANK() OVER(ORDER BY I.itemID) [rowNumber],
		I.itemName,
		(SELECT ((SELECT sum(Inv.TransactionQty) FROM Inventory Inv WHERE Inv.ItemID = I.ItemID) - sum(H.HoldQty)) FROM Holds H WHERE H.ItemID = I.ItemID) AS [totalQuantity],
		(SELECT TOP 1 VW.MarkupCost FROM vw_getCosts VW WHERE VW.ItemID = I.ItemID GROUP BY VW.ItemID, VW.MarkupCost ORDER BY VW.MarkupCost DESC) AS [markupCost],
		IC.ItemCategoryName,
		(SELECT TOP 1 VC.VendorCost FROM VendorCosts VC WHERE VC.ItemID = I.ItemID ORDER BY VC.VendorCost DESC) AS [VendorCost],
		SUBU.subUnitName
	FROM
		ITEMS I
		INNER JOIN ItemCategories IC ON IC.itemCategoryID = I.itemCategoryID
		INNER JOIN StoreUnitItems SUI ON SUI.itemID = I.itemID
		INNER JOIN StoreUnits SU ON SU.storeUnitID = SUI.storeUnitID
			AND SU.subUnitID = @subUnitID
		INNER JOIN SubUnits SUBU ON SUBU.subUnitId = SU.subUnitID
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_reports_currentItemsByCategory')

DROP PROCEDURE sp_reports_currentItemsByCategory

GO

--Created by Michael Meyer July 18, 2012
CREATE PROCEDURE [dbo].[sp_reports_currentItemsByCategory] (
	@categoryID INT
)
AS
BEGIN

	SELECT RANK() OVER(ORDER BY Inv.TransactionDate) [rowNumber], Inv.TransactionDate, I.ItemID, I.ItemName, Inv.TransactionQty, S.StoreName, IC.ItemCategoryName, V.VendorName, VC.VendorCost
		FROM Inventory AS Inv
		JOIN Items I ON I.ItemID = Inv.ItemID
		JOIN VendorCosts VC ON VC.VendorCostID = Inv.VendorCostID
		JOIN Vendors V ON V.VendorID = VC.VendorID
		JOIN ItemCategories IC ON IC.ItemCategoryID = I.ItemCategoryID
		JOIN Stores S ON IC.StoreID = S.StoreID
		WHERE IC.ItemCategoryID = @categoryID
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_reports_currentItems')

DROP PROCEDURE sp_reports_currentItems

GO

--Created by Michael Meyer July 18, 2012
CREATE PROCEDURE [dbo].[sp_reports_currentItems]
AS
BEGIN

	SELECT
		RANK() OVER(ORDER BY I.itemID) [rowNumber],
		I.itemName,
		(SELECT ((SELECT sum(Inv.TransactionQty) FROM Inventory Inv WHERE Inv.ItemID = I.ItemID) - sum(H.HoldQty)) FROM Holds H WHERE H.ItemID = I.ItemID) AS [totalQuantity],
		--I.totalQuantity,
		(SELECT TOP 1 VW.MarkupCost FROM vw_getCosts VW WHERE VW.ItemID = I.ItemID GROUP BY VW.ItemID, VW.MarkupCost ORDER BY VW.MarkupCost DESC) AS [markupCost],
		IC.ItemCategoryName
	FROM
		ITEMS I
		INNER JOIN ItemCategories IC ON IC.itemCategoryID = I.itemCategoryID
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_reports_currentItemByItemID')

DROP PROCEDURE sp_reports_currentItemByItemID

GO

--Created by Michael Meyer July 18, 2012
CREATE PROCEDURE [dbo].[sp_reports_currentItemByItemID] (
	@itemID INT
)

AS
BEGIN

	SELECT RANK() OVER(ORDER BY Inv.TransactionDate) [rowNumber], Inv.TransactionDate, I.ItemID, I.ItemName, Inv.TransactionQty, S.StoreName, IC.ItemCategoryName, V.VendorName, VC.VendorCost
		FROM Inventory AS Inv
		JOIN Items I ON I.ItemID = Inv.ItemID
		JOIN VendorCosts VC ON VC.VendorCostID = Inv.VendorCostID
		JOIN Vendors V ON V.VendorID = VC.VendorID
		JOIN ItemCategories IC ON IC.ItemCategoryID = I.ItemCategoryID
		JOIN Stores S ON IC.StoreID = S.StoreID
		WHERE I.ItemID = @itemID
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_report_itemReportForDateRange')

DROP PROCEDURE sp_report_itemReportForDateRange

GO

CREATE PROCEDURE [dbo].[sp_report_itemReportForDateRange] (
	@startDate DATETIME,
		--SET @startDate = '2/1/2012'
	@endDate DATETIME,
		--SET @endDate = '3/31/2012'
	@preparedBy NVARCHAR(MAX)
		--SET @preparedBy = 'Test'
)
AS
BEGIN
	DECLARE @monthName NVARCHAR(MAX)
		SET @monthName = (
		--Gets the earliest month that the report is run for
			SELECT
				X.[monthName]
			FROM (
				SELECT 
					ROW_NUMBER() OVER (ORDER BY CB.monthOfYear) [rowNum],
					MAX(CB.[monthName]) [monthName]
				FROM
					calendarBasic CB
				WHERE
					CB.[dateName] BETWEEN @startDate AND @EndDate
				GROUP BY
					CB.monthOfYear
			) X
			WHERE
				X.rowNum = 1
		)

	;WITH CTE_items AS (
		SELECT
			I.itemID,
			I.itemName,
			(SELECT ((SELECT sum(Inv.TransactionQty) FROM Inventory Inv WHERE Inv.ItemID = I.ItemID) - sum(H.HoldQty)) FROM Holds H WHERE H.ItemID = I.ItemID) AS [totalQuantity]
		FROM
			Items I
	),

	CTE_requestItemsReceived AS (
		SELECT
			SRI.ItemID,
			SUM(CASE WHEN RRI.ReceivedDate > @startDate THEN ISNULL(RRI.QtyReceived, 0) ELSE 0 END) [itemsRemovedBeginningOfPer],
			SUM(CASE WHEN RRI.ReceivedDate > @endDate THEN ISNULL(RRI.QtyReceived, 0) ELSE 0 END) [itemsRemovedEndOfPer],
			SUM(RRI.QtyReceived) [received]
		FROM
			requestReceivedItems RRI
			INNER JOIN StoreRequestItems SRI ON SRI.StoreRequestItemID = RRI.StoreRequestItemID
		WHERE
			RRI.ReceivedDate >= @startDate
		GROUP BY
			SRI.ItemID
	),

	CTE_requisitionReceivedItems AS (
		SELECT
			RI.ItemID,
			SUM(CASE WHEN RRI.ReceivedDate > @startDate THEN ISNULL(RRI.QtyReceived, 0) ELSE 0 END) [itemsReceivedBeginningOfPer],
			SUM(CASE WHEN RRI.ReceivedDate > @endDate THEN ISNULL(RRI.QtyReceived, 0) ELSE 0 END) [itemsReceivedEndOfPer],
			SUM(RRI.QtyReceived) * -1 [received]
		FROM
			requisitionReceivedItems RRI
			INNER JOIN RequisitionItems RI ON RI.RequisitionItemID = RRI.RequisitionItemID
		WHERE
			RRI.ReceivedDate >= @startDate
		GROUP BY
			RI.ItemID
	),

	CTE_vendorItems AS (
		SELECT
			VP.ItemID,
			(SELECT TOP 1 VC.VendorCost FROM VendorCosts VC WHERE VC.ItemID = VP.ItemID ORDER BY VC.VendorCost DESC) [avgCost]
		FROM
			VendorCosts VP
		WHERE
			VP.ModifiedDate < @endDate
		GROUP BY
			VP.ItemID
	)



	SELECT
		@monthName [month],
		@preparedBy [preparedBy],
		RANK() OVER(ORDER BY I.itemID) [rowNumber],
		I.ItemName,
		TotalQuantity + ISNULL(R.itemsRemovedBeginningOfPer, 0) - ISNULL(RRI.itemsReceivedBeginningOfPer, 0) [balanceBroughtForward],
		(ISNULL(RRI.itemsReceivedBeginningOfPer, 0) - ISNULL(RRI.itemsReceivedEndOfPer, 0)) [received],
		(ISNULL(R.itemsRemovedBeginningOfPer, 0) - ISNULL(R.itemsRemovedEndOfPer, 0)) [issued],
		TotalQuantity + ISNULL(R.itemsRemovedEndOfPer, 0) - ISNULL(RRI.itemsReceivedEndOfPer, 0) [balanceCarriedForward],
		VI.avgCost [rate],
		(ISNULL(TotalQuantity, 0) + ISNULL(R.itemsRemovedEndOfPer, 0) - ISNULL(RRI.itemsReceivedEndOfPer, 0)) * VI.avgCost [productValuePer],
		SUM((ISNULL(TotalQuantity, 0) + ISNULL(R.itemsRemovedEndOfPer, 0) - ISNULL(RRI.itemsReceivedEndOfPer, 0)) * VI.avgCost) OVER(PARTITION BY 1) [totalValueForPeriod]
	FROM
		CTE_Items I
		LEFT JOIN CTE_requestItemsReceived R ON R.itemID = I.itemID
		LEFT JOIN CTE_requisitionReceivedItems RRI ON RRI.itemID = I.itemID
		LEFT JOIN CTE_vendorItems VI ON VI.itemID = I.itemID
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_rejectRequisition')

DROP PROCEDURE sp_rejectRequisition

GO

--Created by Preston Berrett June 26, 2012
CREATE PROCEDURE [dbo].[sp_rejectRequisition]
	@RequisitionID INT
AS
BEGIN
DECLARE @results NVARCHAR(100)
	IF NOT EXISTS (
		SELECT
			RI.RequisitionItemID
		FROM
			RequisitionItems RI
		WHERE
			RI.RequisitionID = @RequisitionID
	)
	BEGIN --if there aren't any pending requisitions
		SET @results = 'No Requisitions';
		
		SELECT
			@results;
	END
	ELSE
	BEGIN
		
		UPDATE RequisitionItems
		SET	Denied = 0
		WHERE 
			RequisitionID = @RequisitionID
		
		SET @results = NULL;
		
		SELECT
			@results; 
			
		SELECT
			RI.RequisitionItemID
		FROM
			RequisitionItems RI
		WHERE
			RI.RequisitionID = @RequisitionID
	END
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_reallocateItem')

DROP PROCEDURE sp_reallocateItem

GO

--Created by Preston Berrett July 19, 2012
CREATE PROCEDURE [dbo].[sp_reallocateItem]
	@ItemID INT,
	@StoreID INT,
	@SubUnitFromID INT,
	@SubUnitToID INT,
	@Qty INT
AS
BEGIN
DECLARE @results NVARCHAR(100)
	IF NOT EXISTS (
		SELECT
			*
		FROM
			StoreUnits SU
		WHERE
			SU.StoreID = @StoreID
			AND 
			SU.SubUnitID = @SubUnitFromID
	)
	BEGIN -- if there is no requisition with that item
		SET @results = 'That store and subunit do not exist';
		
		SELECT
			@results;
	END
	ELSE
	BEGIN
		
		SET @results = NULL;
		
		SELECT
			@results;
			
		UPDATE SUI
		SET SUI.StoreUnitQty = SUI.StoreUnitQty + @Qty
		FROM StoreUnitItems SUI
			INNER JOIN StoreUnits SU ON SU.StoreUnitID = SUI.StoreUnitID
		WHERE 
			SUI.ItemID = @ItemID
			AND
			SU.StoreID = @StoreID
			AND
			SU.SubUnitID = @SubUnitToID;
			
		UPDATE SUI
		SET SUI.StoreUnitQty = SUI.StoreUnitQty - @Qty
		FROM StoreUnitItems SUI
			INNER JOIN StoreUnits SU ON SU.StoreUnitID = SUI.StoreUnitID
		WHERE 
			SUI.ItemID = @ItemID
			AND
			SU.StoreID = @StoreID
			AND
			SU.SubUnitID = @SubUnitFromID;
			
		SELECT
			'The item has been reallocated'
	END
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_modifyVendor')

DROP PROCEDURE sp_modifyVendor

GO

--Created by Michael Meyer July 9, 2012
CREATE PROCEDURE [dbo].[sp_modifyVendor] (
	@vendorID INT,
	@vendorName NVARCHAR(100),
	@vendorAddress NVARCHAR(100),
	@vendorPhone NVARCHAR(100),
	@statusID INT
)
AS
BEGIN
DECLARE @results NVARCHAR(100)
	SET @results = 'Unknown Error'
DECLARE @completed bit
	SET @completed = 0
	
--Checks to see if the address should be nulled
IF(@vendorAddress = '-1')
BEGIN
	SET @vendorAddress = NULL
END

--Checks to see if the phone number should be nulled
IF(@vendorPhone = '-1')
BEGIN
	SET @vendorPhone = NULL
END

--Checks to see if the status ID is valid for the vendor
IF(@statusID IN (
	SELECT
		S.statusID
	FROM
		StatusTypes ST
		INNER JOIN Statuses S ON S.statusTypeID = ST.statusTypeID
	WHERE
		ST.statusTypeName = 'Vendor'
	))
	BEGIN --If the status is valid
		--Checks to see if the vendor exists
		IF EXISTS(
			SELECT
				V.vendorID
			FROM
				Vendors V
			WHERE
				V.vendorID = @vendorID
		)
		BEGIN --The vendor exists
			UPDATE V
				SET V.vendorName = @vendorName,
					V.vendorAddress = @vendorAddress,
					V.phoneNumber = @vendorPhone,
					V.statusID = @statusID
			FROM
				Vendors V
			WHERE
				V.vendorID = @vendorID
			
			SET @results = NULL
			SET @completed = 1	
				
		END
		ELSE --the vendor doesn't exist
		BEGIN
			SET @results = 'Not a valid vendor.'
			SET @completed = 0
		END
	END
	ELSE --if the status is not valid
	BEGIN 
		SET @results = 'Not a valid status.'
		SET @completed = 0
	END
	
	SELECT
		@results [errorMessage]
			
	SELECT
		@completed [true_false]
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_modifyUsers')

DROP PROCEDURE sp_modifyUsers

GO

--Created by Benjamin Strickler June 5 2012
CREATE PROCEDURE [dbo].[sp_modifyUsers] (
	@UserID				int,
	@FirstName			varchar(20),
	@LastName			varchar(20),
	@UserName			nvarchar(20),
	@PasswordHint		nvarchar(20),
	@storeID			int,
	@statusID			int,
	@isAdmin			bit,
	@isGeneralManager	bit,
	@isStoreManager		bit,
	@isStoreKeeper		bit,
	@isSubUnitClerk		bit,
	@isCEO				bit,
	@isAccounting		bit,
	@isProcurement		bit

)

AS

BEGIN
	IF EXISTS (SELECT U.UserName FROM Users U WHERE U.Username = @UserName AND UserID != @UserID)
		BEGIN
			SELECT
			'The username already exists' [errors]
		END
	ELSE
		BEGIN
			IF(@storeID = -1)
			BEGIN
				SET @storeID = NULL
			END
		
			UPDATE Users
			SET 
				FirstName		= @FirstName,
				LastName		= @LastName,
				Username		= @UserName,
				PasswordHint	= @PasswordHint,
				statusID		= @statusID,
				storeID			= @storeID
			WHERE 
				UserID			= @UserID
				
			IF (@isAdmin = 1)
				BEGIN
					IF NOT EXISTS (SELECT UserRoleID FROM UserRoles UR INNER JOIN Roles R ON R.roleID = UR.roleID WHERE UR.UserID = @UserID AND UR.RoleID = 1) 
						BEGIN
							INSERT INTO [dbo].[UserRoles] ([UserID], [RoleID])
							VALUES (@UserID, 1)
						END
				END
			ELSE
				BEGIN
					IF EXISTS (SELECT UserRoleID FROM UserRoles UR INNER JOIN Roles R ON R.roleID = UR.roleID WHERE UR.UserID = @UserID AND UR.RoleID = 1)
					DELETE FROM UserRoles WHERE UserID = @UserID AND RoleID = 1
				END
			IF (@isGeneralManager = 1)
				BEGIN
					IF NOT EXISTS (SELECT UserRoleID FROM UserRoles UR INNER JOIN Roles R ON R.roleID = UR.roleID WHERE UR.UserID = @UserID AND UR.RoleID = 2) 
						BEGIN
							INSERT INTO [dbo].[UserRoles] ([UserID], [RoleID])
							VALUES (@UserID, 2)
						END
				END
			ELSE
				BEGIN
					IF EXISTS (SELECT UserRoleID FROM UserRoles UR INNER JOIN Roles R ON R.roleID = UR.roleID WHERE UR.UserID = @UserID AND UR.RoleID = 2)
					DELETE FROM UserRoles WHERE UserID = @UserID AND RoleID = 2
				END
			IF (@isStoreManager = 1)
				BEGIN
					IF NOT EXISTS (SELECT UserRoleID FROM UserRoles UR INNER JOIN Roles R ON R.roleID = UR.roleID WHERE UR.UserID = @UserID AND UR.RoleID = 3) 
						BEGIN
							INSERT INTO [dbo].[UserRoles] ([UserID], [RoleID])
							VALUES (@UserID, 3)
						END
				END
			ELSE
				BEGIN
					IF EXISTS (SELECT UserRoleID FROM UserRoles UR INNER JOIN Roles R ON R.roleID = UR.roleID WHERE UR.UserID = @UserID AND UR.RoleID = 3)
					DELETE FROM UserRoles WHERE UserID = @UserID AND RoleID = 3
				END
			IF (@isStoreKeeper = 1)
				BEGIN
					IF NOT EXISTS (SELECT UserRoleID FROM UserRoles UR INNER JOIN Roles R ON R.roleID = UR.roleID WHERE UR.UserID = @UserID AND UR.RoleID = 4) 
						BEGIN
							INSERT INTO [dbo].[UserRoles] ([UserID], [RoleID])
							VALUES (@UserID, 4)
						END
				END
			ELSE
				BEGIN
					IF EXISTS (SELECT UserRoleID FROM UserRoles UR INNER JOIN Roles R ON R.roleID = UR.roleID WHERE UR.UserID = @UserID AND UR.RoleID = 4)
					DELETE FROM UserRoles WHERE UserID = @UserID AND RoleID = 4
				END
			IF (@isSubUnitClerk = 1)
				BEGIN
					IF NOT EXISTS (SELECT UserRoleID FROM UserRoles UR INNER JOIN Roles R ON R.roleID = UR.roleID WHERE UR.UserID = @UserID AND UR.RoleID = 5) 
						BEGIN
							INSERT INTO [dbo].[UserRoles] ([UserID], [RoleID])
							VALUES (@UserID, 5)
						END
				END
			ELSE
				BEGIN
					IF EXISTS (SELECT UserRoleID FROM UserRoles UR INNER JOIN Roles R ON R.roleID = UR.roleID WHERE UR.UserID = @UserID AND UR.RoleID = 5)
					DELETE FROM UserRoles WHERE UserID = @UserID AND RoleID = 5
				END
			IF (@isCEO = 1)
				BEGIN
					IF NOT EXISTS (SELECT UserRoleID FROM UserRoles UR INNER JOIN Roles R ON R.roleID = UR.roleID WHERE UR.UserID = @UserID AND UR.RoleID = 6) 
						BEGIN
							INSERT INTO [dbo].[UserRoles] ([UserID], [RoleID])
							VALUES (@UserID, 6)
						END
				END
			ELSE
				BEGIN
					IF EXISTS (SELECT UserRoleID FROM UserRoles UR INNER JOIN Roles R ON R.roleID = UR.roleID WHERE UR.UserID = @UserID AND UR.RoleID = 6)
					DELETE FROM UserRoles WHERE UserID = @UserID AND RoleID = 6
				END
			IF (@isAccounting = 1)
				BEGIN
					IF NOT EXISTS (SELECT UserRoleID FROM UserRoles UR INNER JOIN Roles R ON R.roleID = UR.roleID WHERE UR.UserID = @UserID AND UR.RoleID = 7) 
						BEGIN
							INSERT INTO [dbo].[UserRoles] ([UserID], [RoleID])
							VALUES (@UserID, 7)
						END
				END
			ELSE
				BEGIN
					IF EXISTS (SELECT UserRoleID FROM UserRoles UR INNER JOIN Roles R ON R.roleID = UR.roleID WHERE UR.UserID = @UserID AND UR.RoleID = 7)
					DELETE FROM UserRoles WHERE UserID = @UserID AND RoleID = 7
				END
			IF (@isProcurement = 1)
				BEGIN
					IF NOT EXISTS (SELECT UserRoleID FROM UserRoles UR INNER JOIN Roles R ON R.roleID = UR.roleID WHERE UR.UserID = @UserID AND UR.RoleID = 8) 
						BEGIN
							INSERT INTO [dbo].[UserRoles] ([UserID], [RoleID])
							VALUES (@UserID, 8)
						END
				END
			ELSE
				BEGIN
					IF EXISTS (SELECT UserRoleID FROM UserRoles UR INNER JOIN Roles R ON R.roleID = UR.roleID WHERE UR.UserID = @UserID AND UR.RoleID = 8)
					DELETE FROM UserRoles WHERE UserID = @UserID AND RoleID = 8
				END
			SELECT
				NULL [errors]
			SELECT
				'True' [Worked]
		END
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_modifySubUnit')

DROP PROCEDURE sp_modifySubUnit

GO

--created by michael meyer june 25, 2012
CREATE PROCEDURE [dbo].[sp_modifySubUnit] (
	@subUnitID INT,
	@subUnitName NVARCHAR(100),
	@statusID INT
)
AS
BEGIN
	DECLARE @inactiveSubUnitStatusID INT
	--Tracks if we need to inactivate store units that use the subunit
	DECLARE @inactivateStoreUnits BIT
		SET @inactivateStoreUnits = 0
		
	--Checks to see if the statusID is the inactive subUnit status
	IF @statusID = (
		SELECT
			S.statusID
		FROM
			Statuses S
			INNER JOIN StatusTypes ST ON ST.statusTypeID = S.statusTypeID
				AND ST.statusTypeName = 'SubUnit'
				AND S.statusName = 'Inactive'
	)
	BEGIN
		SET @inactivateStoreUnits = 1
		SET @inactiveSubUnitStatusID = (
			SELECT
				S.statusID
			FROM
				Statuses S
				INNER JOIN StatusTypes ST ON ST.statusTypeID = S.statusTypeID
					AND ST.statusTypeName = 'StoreUnit'
					AND S.statusName = 'Inactive'
		)
	END

	IF EXISTS (
		SELECT TOP 1
				S.statusID
			FROM
				statusTypes ST
				INNER JOIN STATUSES S ON S.statusTypeID = ST.statusTypeID
			WHERE
				ST.statusTypeName = 'SubUnit'
				AND S.statusID = @statusID
		)
	BEGIN
		SET @statusID = (
			SELECT TOP 1
				S.statusID
			FROM
				statusTypes ST
				INNER JOIN STATUSES S ON S.statusTypeID = ST.statusTypeID
			WHERE
				ST.statusTypeName = 'SubUnit'
				AND S.statusID = @statusID
		)
		--error results
		DECLARE @results NVARCHAR(100)
		DECLARE @completed BIT

		SET @completed = 0
		SET @results = 'Unknown Error'

		IF NOT EXISTS (
			SELECT
				SU.subUnitID
			FROM
				SubUnits SU
			WHERE
				SU.subUnitID = @subUnitID
			)
			BEGIN --if the the subUnit doesn't exist
				SET @results = 'Sub Unit does not exists.'
				
				SET @completed = 0
			END
			
			ELSE --if the item exists
			
			BEGIN
				SET @results = NULL
				
				UPDATE SU
					SET SU.subUnitName = @subUnitName,
						SU.statusID = @statusID
				FROM
					subUnits SU
				WHERE
					SU.subUnitID = @subUnitID
					
				IF(@inactivateStoreUnits = 1)
				BEGIN
					UPDATE SU
						SET SU.statusID = @inactiveSubUnitStatusID
					FROM
						StoreUnits SU
					WHERE
						SU.subUnitID = @subUnitID
				END
					
				SET @completed = 1
			END

			SELECT
				@results [errorMessage]
			
			SELECT
				@completed [successOrFailure]
	END
	ELSE
	BEGIN
		SET @results = 'Not a valid status id'
		SET @completed = 0
		SELECT
			@results [errorMessage]
		
		SELECT
			@completed [successOrFailure]
	END
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_modifyStoreUnit')

DROP PROCEDURE sp_modifyStoreUnit

GO

--Created by Michael Meyer June 25 2012
CREATE PROCEDURE [dbo].[sp_modifyStoreUnit] (
	@storeID INT,
	@subUnitID INT,
	@statusID INT
)
AS
BEGIN

	IF EXISTS (
		SELECT TOP 1
				S.statusID
			FROM
				statusTypes ST
				INNER JOIN STATUSES S ON S.statusTypeID = ST.statusTypeID
			WHERE
				ST.statusTypeName = 'StoreUnit'
				AND S.statusID = @statusID
		)
	BEGIN

		DECLARE @results NVARCHAR(100)
		SET @results = 'Unknown Error.'

		DECLARE @completed bit
		SET @completed = 0

		DECLARE @storeExists bit

		DECLARE @subUnitExists bit

		--Checks to see if the store exists
		IF EXISTS (
			SELECT
				S.storeID
			FROM
				stores S
			WHERE
				S.storeID = @storeID
				AND S.storeTypeID = 1 --Central store
		)
		BEGIN
			SET @storeExists = 1
		END
		ELSE
		BEGIN
			SET @storeExists = 0
			SET @results = 'Store does not exist.'
		END

		--checks to see if the sub unit exists
		IF EXISTS (
			SELECT
				SU.subUnitID
			FROM
				subUnits SU
			WHERE
				SU.subUnitID = @subUnitID
		)
		BEGIN
			SET @subUnitExists = 1
		END
		ELSE
		BEGIN
			SET @subUnitExists = 0
			SET @results = 'Sub unit does not exist.'
		END

	--if the store and the sub unit exist
		IF(@storeExists = 1 AND @subUnitExists = 1)
		BEGIN
			IF EXISTS(
				SELECT
					SU.storeUnitID
				FROM
					storeUnits SU
				WHERE
					SU.subUnitID = @subUnitID
					AND SU.storeID = @storeID
				)
			BEGIN
				UPDATE SU
					SET SU.statusID = @statusID
				FROM
					storeUnits SU
				WHERE
					SU.storeID = @storeID
					AND SU.subUnitID = @subUnitID
				
				SET @completed = 1
				SET @results = NULL
			END
			ELSE
			BEGIN
				SET @completed = 0
				
				SET @results = 'Store and sub unit do not exist.'
			END
		END
		
		SELECT
			@results [ErrorMessage]
		
		SELECT
			@completed
	END
	ELSE
	BEGIN
		SET @results = 'Not a valid status id'
		SET @completed = 0
		SELECT
			@results [errorMessage]
		
		SELECT
			@completed [successOrFailure]
	END
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_modifyStore')

DROP PROCEDURE sp_modifyStore

GO

--Created by Michael Meyer June 25 2012
CREATE PROCEDURE [dbo].[sp_modifyStore] (
	@storeID INT,
	@storeName NVARCHAR(100),
	@storeTypeID INT,
	@statusID INT
)
AS
BEGIN
	DECLARE @inactiveStoreUnitStatusID INT
	--Tracks if we need to inactivate store units
	DECLARE @inactivateStoreUnits BIT
		SET @inactivateStoreUnits = 0
		
	--Checks to see if the statusID is the inactive store status
	IF @statusID = (
		SELECT
			S.statusID
		FROM
			Statuses S
			INNER JOIN StatusTypes ST ON ST.statusTypeID = S.statusTypeID
				AND ST.statusTypeName = 'Store'
				AND S.statusName = 'Inactive'
	)
	BEGIN
		SET @inactivateStoreUnits = 1
		SET @inactiveStoreUnitStatusID = (
			SELECT
				S.statusID
			FROM
				Statuses S
				INNER JOIN StatusTypes ST ON ST.statusTypeID = S.statusTypeID
					AND ST.statusTypeName = 'StoreUnit'
					AND S.statusName = 'Inactive'
		)
		
	END

	DECLARE @results NVARCHAR(100)
	SET @results = 'Unknown Error.'

	DECLARE @completed BIT
	SET @completed = 0
	
	--Checks to see if the store exists
	IF EXISTS (
		SELECT
			S.storeID
		FROM
			stores S
		WHERE
			S.storeID = @storeID
			AND S.storeTypeID = @storeTypeID
	)
	BEGIN
		SET @results = NULL
			
		UPDATE stores
			SET 
				storeName = @storeName,
				StatusID = @statusID
		WHERE
			storeID = @storeID
			AND storeTypeID = @storeTypeID
		
		--Because the store is being inactivated, we need to deactivate all store units	
		IF(@inactivateStoreUnits = 1)
		BEGIN
			UPDATE SU
				SET SU.statusID = @inactiveStoreUnitStatusID
			FROM
				StoreUnits SU
			WHERE
				SU.storeID = @storeID
		END
		
		SET @completed = 1
	END
	ELSE
	BEGIN
		SET @results = 'There is no store for that ID and store type.'
		
		SET @completed = 0
	END
	
	SELECT
		@results [ErrorMessage]
			
	SELECT
		@completed
END

GO


IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_modifyItem')

DROP PROCEDURE sp_modifyItem

GO
--created by michael meyer june 12, 2012
--Adam Venezia 11-24-12
CREATE PROCEDURE [dbo].[sp_modifyItem] (
	@itemID INT,
	@itemName NVARCHAR(100),
	@unitID INT,
	@vendorCost smallmoney,
	@itemCategoryID INT,
	@emergencyQuantity INT,
	@storeID INT,
	@markupPercent FLOAT,
	@vendorID INT,
	@statusID INT
)
AS
BEGIN
--error results
DECLARE @results NVARCHAR(100)
DECLARE @completed BIT

SET @completed = 0
SET @results = 'Unknown Error'
		
IF NOT EXISTS (
	SELECT
		I.itemID
	FROM
		Items I
	WHERE
		I.itemID = @itemID
	)
	BEGIN --if the the item does not exist
		SET @results = 'Item does not exists'
		--didn't complete
		SET @completed = 0
	END
	
ELSE --if the item exists
	
	BEGIN
		SET @results = NULL

		--Update the items table, the homebase of items.
		UPDATE ITEMS
			SET itemName = @itemName,
				unitID = @unitID,
				itemCategoryID = @itemCategoryID,
				EmergencyQty = @emergencyQuantity,
				StatusID = @statusID
			WHERE
				itemID = @itemID

		--Update VendorCosts, the part where items have a cost (shocker... lol)
		UPDATE VendorCosts
			SET VendorID = @vendorID,
				ModifiedDate = GETDATE(),
				VendorCost = @vendorCost,
				MarkupMultiplier = @markupPercent
			WHERE ItemID = @itemID

		--Update availability to store units
		UPDATE StoreUnitItems
			SET StoreUnitQty = @emergencyQuantity,
				StoreUnitID = SU.storeUnitID
			--this update magic is where I'm mostly concerned.
			FROM
				Items I
				INNER JOIN ItemCategories IC ON I.ItemCategoryID = IC.ItemCategoryID
				INNER JOIN StoreUnitItems SUI ON SUI.itemID = I.itemID
				INNER JOIN StoreUnits SU ON SU.storeID = IC.storeID
					AND SU.storeUnitID = SUI.storeUnitID
					AND SU.storeID = @storeID
					AND I.itemID = @itemID
			WHERE SUI.ItemID = @itemID
				
		SET @completed = 1
	END

	SELECT
		@results [errorMessage]
	
	SELECT
		@completed [successOrFailure]
END

GO


IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_modifyCategory')

DROP PROCEDURE sp_modifyCategory

GO

CREATE PROCEDURE [dbo].[sp_modifyCategory]
	@categoryID INT,
	@categoryName VARCHAR(50),
	@storeID INT
AS

BEGIN
	DECLARE @table0 VARCHAR(50)
	DECLARE @table1 BIT

	IF NOT EXISTS (
			SELECT
				*
			FROM
				ItemCategories
			WHERE
				ItemCategoryID = @categoryID
			)
	BEGIN
		SET @table0 = 'Error with Category'
	END
	ELSE
		BEGIN
			UPDATE ItemCategories
			SET ItemCategoryName = @categoryName, 
			StoreID = @storeID
			WHERE ItemCategoryID = @categoryID
			
			SET @table0 = NULL
			
			SET @table1 = 1
		END
	
	SELECT
		@table0
		
	SELECT
		@table1

END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_MarkRequestItemClosed')

DROP PROCEDURE sp_MarkRequestItemClosed

GO

--Create by Benjamin Strickler Aug. 12 2012
CREATE PROCEDURE [dbo].[sp_MarkRequestItemClosed] (
	@RequestID		int,
	@ItemID			int
)
AS

BEGIN
	UPDATE StoreRequestItems
	SET StatusID = 94
	WHERE SatelliteStoreRequestID = @RequestID
	AND ItemID = @ItemID
	
	IF NOT EXISTS (SELECT * FROM StoreRequestItems WHERE SatelliteStoreRequestID = @RequestID AND StatusID != 94 AND StatusID != 93)
	BEGIN
		UPDATE SatelliteStoreRequests
		SET StatusID = 81
		WHERE SatelliteStoreRequestID = @RequestID
	END
	
END

GO

--created by travis marceau to remove stock from available inventory
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_CreateHold')

DROP PROCEDURE sp_CreateHold

GO

CREATE PROCEDURE [dbo].[sp_CreateHold] (
	@HoldRequestID			int,
	@HoldItemID				int,
	@HoldApprovedQuantity	int
)
AS

BEGIN
		
		INSERT INTO Holds (StoreRequestItemID, ItemID, HoldQty)
			VALUES (@HoldRequestID, @HoldItemID, @HoldApprovedQuantity)

END

GO


IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_markItemReady')

DROP PROCEDURE sp_markItemReady

GO

/* Written by Preston Berrett July 19, 2012 */
CREATE PROCEDURE [dbo].[sp_markItemReady]
	@RequestID		int,
	@RequestItemID	int,
	@ItemID			int,
	@Allocated		int -- Added to account to keep track of the amount of items Allowed (Allocated)
AS
BEGIN
	DECLARE @results NVARCHAR(100)
	IF NOT EXISTS (
			SELECT 
				* 
			FROM 
				 StoreRequestItems
			WHERE 
				SatelliteStoreRequestID = @RequestID
				AND ItemID = @ItemID)
	BEGIN
		SET @results = 'Requisition does not exist';
			
		SELECT
		@results;
	END
	ELSE
	BEGIN

		EXEC sp_CreateHold @HoldRequestID = @RequestItemID, @HoldItemID = @ItemID, @HoldApprovedQuantity = @Allocated

		--defaulting to 0 using ISNULL(query, 0) solves issues with the control logic below.
		DECLARE @currAlloc int
		SET @currAlloc = ISNULL((SELECT Allocated FROM StoreRequestItems 
							WHERE SatelliteStoreRequestID = @RequestID
							AND ItemID = @ItemID), 0)
		
		IF (SELECT QtyRequested FROM StoreRequestItems
				WHERE SatelliteStoreRequestID = @RequestID
				AND ItemID = @ItemID) = @currAlloc + @Allocated
		BEGIN
		UPDATE StoreRequestItems
		SET StatusID = 92 /*Ready*/
		WHERE
			SatelliteStoreRequestID = @RequestID
			AND
			ItemID = @ItemID
		END
		ELSE
		BEGIN
		UPDATE StoreRequestItems
		SET StatusID = 112 /*Partially Approved*/
		WHERE
			SatelliteStoreRequestID = @RequestID
			AND
			ItemID = @ItemID
		END
		
		IF @currAlloc != 0 --remember, we called ISNULL(query, 0), so instead of checking for IS NOT NULL, check for 0 now.
		BEGIN
		UPDATE StoreRequestItems
		SET Allocated = @currAlloc + @Allocated -- Sets the amount approved (allocated)
		WHERE
			SatelliteStoreRequestID = @RequestID
			AND
			ItemID = @ItemID
		
		SET @results = 'Item status has been updated';
		END
		ELSE
		BEGIN
		UPDATE StoreRequestItems
		SET Allocated = @Allocated -- Sets the amount approved (allocated)
		WHERE
			SatelliteStoreRequestID = @RequestID
			AND
			ItemID = @ItemID
		
		SET @results = 'Item status has been updated';
		END

		SELECT
		@results;
	END
END

GO


IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getAllocatedQty')

DROP PROCEDURE sp_getAllocatedQty

GO

/* Written by Preston Berrett July 19, 2012 */
CREATE PROCEDURE [dbo].[sp_getAllocatedQty]
	@RequestItemID	int
AS
BEGIN
	DECLARE @results NVARCHAR(100)
	IF NOT EXISTS (
			SELECT 
				* 
			FROM 
				 StoreRequestItems
			WHERE 
				StoreRequestItemID = @RequestItemID)
	BEGIN
		SET @results = 'Requisition does not exist';
			
		SELECT
		@results;
	END
	ELSE
	BEGIN
		DECLARE @currAlloc int
		SET @currAlloc = (SELECT Allocated FROM StoreRequestItems 
							WHERE StoreRequestItemID = @RequestItemID)		
		
		SET @results = @currAlloc;
			
		SELECT
		@results;
	END
END

GO


IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_markItemOrdered')

DROP PROCEDURE sp_markItemOrdered

GO

/* Written by Preston Berrett July 19, 2012 */
CREATE PROCEDURE [dbo].[sp_markItemOrdered]
	@RequestID		int,
	@ItemID			int
AS
BEGIN
	DECLARE @results NVARCHAR(100)
	IF NOT EXISTS (
			SELECT 
				* 
			FROM 
				 RequisitionItems
			WHERE 
				RequisitionID = @RequestID
				AND ItemID = @ItemID)
	BEGIN
		SET @results = 'Requisition does not exist';
			
		SELECT
		@results;
	END
	ELSE
	BEGIN
		
		UPDATE RequisitionItems
		SET StatusID = 91 /*Ready*/
		WHERE
			RequisitionID = @RequestID
			AND
			ItemID = @ItemID
		
		SET @results = 'Item status has been updated';
		
		IF NOT EXISTS (SELECT * FROM RequisitionItems WHERE RequisitionID = @RequestID AND StatusID != 91 AND Denied = 0)
						
		BEGIN
			UPDATE Requisitions SET StatusID = 44
			WHERE RequisitionID = @RequestID
			SET @results = 'Ordered'; 
		END
			
		SELECT
		@results;
	END
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_logout')

DROP PROCEDURE sp_logout

GO

--Created by Michael Meyer May 28, 2012
CREATE PROCEDURE [dbo].[sp_logout](
	@sessionID NVARCHAR(255)
)
AS 

BEGIN
--this is the error variable
DECLARE @results NVARCHAR(100)
DECLARE @loggedOut BIT

	IF EXISTS (
		SELECT
			LH.sessionGUID
		FROM
			LoginHistory LH
		WHERE
			LH.sessionGUID = @sessionID
	)
		BEGIN --session exists
			SET @results = NULL
			
			SET @loggedOut = 1
			
			UPDATE LoginHistory
				SET EndDate = GETDATE()
			FROM
				LoginHistory LH
			WHERE
				LH.sessionGUID = @sessionID
		END
	
	ELSE
		BEGIN -- session doesnt exist
			SET @results = 'Session does not exist'
			SET @loggedOut = 0
		END
		
	SELECT
		@results [errorMessage]
	
	SELECT
		@loggedOut [loginStatus]
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_Login')

DROP PROCEDURE sp_Login

GO

--Created by Michael Meyer
CREATE PROCEDURE [dbo].[sp_Login]
	@username VARCHAR(50),
	@password VARCHAR(50),
	@ipaddress VARCHAR(20)

AS

BEGIN

	DECLARE @statusID INT
	
	SET @statusID = (
		SELECT TOP 1
			S.statusID
		FROM
			StatusTypes ST
			INNER JOIN statuses S ON S.statusTypeID = ST.statusTypeID
		WHERE
			ST.statusTypeName = 'User'
			AND S.statusName = 'Active'
	)


	DECLARE @sessionGUID NVARCHAR(255)
	DECLARE @userID INT
	DECLARE @result NVARCHAR(100)
	DECLARE @Admin BIT
	DECLARE @GeneralManger BIT
	DECLARE @StoreManager BIT
	DECLARE @StoreKeeper BIT
	DECLARE @SubUnitClerk BIT
	DECLARE @CEO BIT
	DECLARE @Accounting BIT
	DECLARE @Procurement BIT
		
	IF NOT EXISTS (
			SELECT
				U.UserID
			FROM
				Users U
			WHERE
				U.Username = @username
				AND	U.[Pass] = @password
				--AND U.statusID = @statusID --active users
			)
	BEGIN
		SET @result = 'Incorrect Login'
	END
	ELSE 

	BEGIN
	IF NOT EXISTS (
			SELECT
				U.UserID
			FROM
				Users U
			WHERE
				U.Username = @username
				AND	U.[Pass] = @password
				AND U.statusID = @statusID --active users
			)
	BEGIN
		SET @result = 'Inactive User'
	END

	ELSE 
	
	BEGIN
		--this proc updates all the old logins and sets the end date to getdate()
		EXEC sp_updateLoginHistory @username, @password
		--inserts values into login history
		INSERT LoginHistory(UserID, StartDate, SessionGUID, IPAddress)
			SELECT 
				UserID, 
				GETDATE(), 
				NEWID(),
				@ipaddress
			FROM 
				Users 
			WHERE 
				Username = @username;
				
		SET @result = NULL;
		
		IF EXISTS (
			SELECT
				UR.roleID
			FROM
				Users U
				INNER JOIN UserRoles UR ON UR.userID = U.userID
			WHERE
				U.username = @username
				AND U.pass = @password
				AND UR.roleID = 1
			) 
		BEGIN 
			SET @Admin = 1
		END
	
		IF EXISTS (
			SELECT
				UR.roleID
			FROM
				Users U
				INNER JOIN UserRoles UR ON UR.userID = U.userID
			WHERE
				U.username = @username
				AND U.pass = @password
				AND UR.roleID = 2
			) 
			BEGIN 
				SET @GeneralManger = 1
			END

		IF EXISTS (
			SELECT
				UR.roleID
			FROM
				Users U
				INNER JOIN UserRoles UR ON UR.userID = U.userID
			WHERE
				U.username = @username
				AND U.pass = @password
				AND UR.roleID = 3
			) 
			BEGIN 
				SET @StoreManager = 1
			END
			
		IF EXISTS (
			SELECT
				UR.roleID
			FROM
				Users U
				INNER JOIN UserRoles UR ON UR.userID = U.userID
			WHERE
				U.username = @username
				AND U.pass = @password
				AND UR.roleID = 4
			) 
			BEGIN 
				SET @StoreKeeper = 1
			END
			
		IF EXISTS (
			SELECT
				UR.roleID
			FROM
				Users U
				INNER JOIN UserRoles UR ON UR.userID = U.userID
			WHERE
				U.username = @username
				AND U.pass = @password
				AND UR.roleID = 5
			) 
			BEGIN 
				SET @SubUnitClerk = 1
			END
			
		IF EXISTS (
			SELECT
				UR.roleID
			FROM
				Users U
				INNER JOIN UserRoles UR ON UR.userID = U.userID
			WHERE
				U.username = @username
				AND U.pass = @password
				AND UR.roleID = 6
			) 
			BEGIN 
				SET @CEO = 1
			END
			
		IF EXISTS (
			SELECT
				UR.roleID
			FROM
				Users U
				INNER JOIN UserRoles UR ON UR.userID = U.userID
			WHERE
				U.username = @username
				AND U.pass = @password
				AND UR.roleID = 7
			) 
			BEGIN 
				SET @Accounting = 1
			END

		IF EXISTS (
			SELECT
				UR.roleID
			FROM
				Users U
				INNER JOIN UserRoles UR ON UR.userID = U.userID
			WHERE
				U.username = @username
				AND U.pass = @password
				AND UR.roleID = 8
			) 
			BEGIN 
				SET @Procurement = 1
			END
	END
	END
	
	SELECT 
		@result [ErrorMessage]
		
	SELECT
		U.userID [userID],
		U.firstName,
		U.lastName,
		U.StoreID,
		LH.SessionGUID [sessionGUID],
		ISNULL(@Admin, 0) [isAdmin],
		ISNULL(@GeneralManger, 0) [isGeneralManager],
		ISNULL(@StoreManager, 0) [isStoreManager],
		ISNULL(@StoreKeeper, 0) [isStoreKeeper],
		ISNULL(@SubUnitClerk, 0) [isUnitClerk],
		ISNULL(@CEO, 0) [isCEO],
		ISNULL(@Accounting, 0) [isAccounting],
		ISNULL(@Procurement, 0) [isProcurement]
	FROM
		Users U
		INNER JOIN LoginHistory LH ON LH.UserID = U.UserID
	WHERE
		U.Username = @username
		AND LH.StartDate IS NOT NULL
		ANd LH.EndDate IS NULL
		
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_ItemsandQuantity')

DROP PROCEDURE sp_ItemsandQuantity

GO

--Modified by Bryan Nixon Aug 17, 2012
CREATE PROCEDURE [dbo].[sp_ItemsandQuantity] (
	@RequestID int,
	@StoreID int
)
AS

BEGIN
DECLARE @results NVARCHAR(100)
	BEGIN 
		SET @results = NULL

		SELECT
			@results
			
		SELECT
			sri.ItemID, 
			i.ItemName, 
			s.StatusName, 
			sri.qtyRequested, 
			ISNULL(t1.QtyReceived, 0) as 'Received', 
			t2.LastReceivedDate as 'Last Received',
			ISNULL(sri.Allocated, 0) as 'Allocated' -- Added amount approved (allocated) for the request
		FROM
			StoreRequestItems sri
		JOIN
			Items i ON sri.ItemID = i.ItemID
		JOIN
			Statuses s ON sri.StatusID = s.StatusID
		JOIN 
			ItemCategories IC ON I.ItemCategoryID = IC.ItemCategoryID
		FULL JOIN	
			(SELECT
				sri.ItemID,
				sum(QtyReceived) as 'QtyReceived'
			FROM
				StoreRequestItems sri
			JOIN
				RequestReceivedItems rri on sri.StoreRequestItemID = rri.StoreRequestItemID
			WHERE
				sri.SatelliteStoreRequestID = @RequestID
			GROUP BY
				sri.ItemID)t1 ON sri.ItemID = t1.ItemID
		FULL JOIN
			(SELECT
				sri.ItemID,
				max(ReceivedDate) as 'LastReceivedDate'
			FROM
				StoreRequestItems sri
			JOIN
				RequestReceivedItems rri on sri.StoreRequestItemID = rri.StoreRequestItemID
			WHERE
				sri.SatelliteStoreRequestID = @RequestID
			GROUP BY
				sri.ItemID)t2 ON sri.ItemID = t2.ItemID
		WHERE
			sri.SatelliteStoreRequestID = @RequestID
			AND s.StatusName != 'Closed' --Show items after it is Partially Filled
			AND IC.StoreID = @StoreID
	END
	
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_isValidSession')

DROP PROCEDURE sp_isValidSession

GO

CREATE PROCEDURE [dbo].[sp_isValidSession] (
	@sessionID NVARCHAR(100)
)
AS
--error message variable
BEGIN
	DECLARE @results NVARCHAR(100)

	IF EXISTS (
		SELECT
			LH.sessionGUID
		FROM
			LoginHistory LH
		WHERE
			LH.sessionGUID = @sessionID
			AND LH.endDate IS NULL
		)
		BEGIN
			--null for error message
			SET @results = NULL
		END
		
	ELSE--if it is not a valid session
		BEGIN
			SET @results = 'Not a valid Session.'
		END
		
	--error table
	SELECT
		@results

	--results table
	SELECT
		LH.SessionGUID,
		LH.UserID
	FROM
		LoginHistory LH
	WHERE
		LH.SessionGUID = @sessionID
		AND LH.endDate IS NULL
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getYears')

DROP PROCEDURE sp_getYears

GO

--Created by Michael Meyer July 23, 2012
CREATE PROCEDURE [dbo].[sp_getYears] 
AS
BEGIN
	SELECT
		X.[year]
	FROM (
		SELECT DISTINCT
			CB.calendarYear [year]
		FROM
			calendarBasic CB
		WHERE
			CB.calendarYear  = YEAR(GETDATE())
			
		UNION
		
		SELECT
			YEAR(SSR.RequestDate) [year]
		FROM
			SatelliteStoreRequests SSR
			
		UNION
		
		SELECT
			YEAR(OrderDate) [year]
		FROM
			Requisitions	
	) X
	ORDER BY
		X.[year]
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getVendors')

DROP PROCEDURE sp_getVendors

GO

--Created by Michael Meyer July 9, 2012
CREATE PROCEDURE [dbo].[sp_getVendors]
AS
BEGIN

DECLARE @results NVARCHAR(100)

	IF EXISTS( 
		SELECT TOP 1
			V.vendorID
		FROM
			Vendors V
	)
	BEGIN
		SET @results = NULL
	END
	ELSE
	BEGIN
		SET @results = 'No Vendors.'
	END
		
	SELECT
		@results [errorMessage]
		
	SELECT
		V.vendorID,
		V.vendorName,
		V.vendorAddress,
		V.phoneNumber,
		V.statusID
	FROM
		VENDORS V	
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getVendorByID')

DROP PROCEDURE sp_getVendorByID

GO

--Created by Michael Meyer July 11 2012
CREATE PROCEDURE [dbo].[sp_getVendorByID] (
	@vendorID INT
)
AS
BEGIN
DECLARE @results NVARCHAR(100)
	--checks to see if there is a vendor by the ID passed in
	IF EXISTS (
		SELECT
			V.vendorID
		FROM
			Vendors V
		WHERE
			V.vendorID = @vendorID
	)
	BEGIN
		SET @results = NULL
	END
	
	ELSE
	
	BEGIN
		SET @results = 'No vendor by that ID'
	END
	
	SELECT
		@results
		
	SELECT
		V.vendorID,
		V.vendorName,
		V.vendorAddress,
		V.phoneNumber,
		V.statusID
	FROM
		Vendors V
	WHERE
		V.vendorID = @vendorID

END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getUsersByStatusID')

DROP PROCEDURE sp_getUsersByStatusID

GO

--Created by Michael Meyer June 6, 2012
CREATE PROCEDURE [dbo].[sp_getUsersByStatusID] (
	@statusID INT
)
AS
BEGIN
DECLARE @results NVARCHAR(100)
DECLARE @runFinalSelect BIT
	IF NOT EXISTS(
		SELECT
			U.userID
		FROM
			Users U
		WHERE
			U.statusID = @statusID
	)
		BEGIN -- no users under that status
			SET @results = 'No users under that status.'
			SET @runFinalSelect = 0
		END
	ELSE
		BEGIN
			SET @results = NULL
			SET @runFinalSelect = 1
		END
		
	
	SELECT --selects the errors
		@results
	
	IF(@runFinalSelect = 1)
		BEGIN --selects users under that status
			SELECT
				U.userID,
				U.firstName + ' ' + U.lastName [name],
				U.storeID
			FROM
				Users U
			WHERE
				U.statusID = @statusID
		END
	ELSE
		BEGIN --there are no users under that status
			SELECT
				NULL
		END
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getUsersByRole')

DROP PROCEDURE sp_getUsersByRole

GO

--Created by Michael Meyer May 31 2012
CREATE PROCEDURE [dbo].[sp_getUsersByRole] (
	@isAdmin BIT,
	@isGeneralManager BIT,
	@isStoreManager BIT,
	@isStoreKeeper BIT,
	@isSubUnitClerk BIT,
	@isCEO BIT,
	@isAccounting BIT,
	@isProcurement BIT
)
AS
BEGIN

DECLARE @admin NVARCHAR(100)
	if(@isAdmin = 1)
		BEGIN
			SET @admin = 'Admin'
		END
	ELSE
		BEGIN
			SET @admin = NULL
		END
DECLARE @generalManager NVARCHAR(100)
	if(@isGeneralManager = 1)
		BEGIN
			SET @generalManager = 'General Manger'
		END
	ELSE
		BEGIN
			SET @generalManager = NULL
		END
DECLARE @storeManager NVARCHAR(100)
	if(@isStoreManager = 1)
		BEGIN
			SET @storeManager = 'Store Manager'
		END
	ELSE
		BEGIN
			SET @storeManager = NULL
		END
DECLARE @storeKeeper NVARCHAR(100)
	if(@isStoreKeeper = 1)
		BEGIN
			SET @storeKeeper = 'Store Keeper'
		END
	ELSE
		BEGIN
			SET @storeKeeper = NULL
		END
DECLARE @subUnitClerk NVARCHAR(100)
	if(@isSubUnitClerk = 1)
		BEGIN
			SET @subUnitClerk = 'SubUnit Clerk'
		END
	ELSE
		BEGIN
			SET @subUnitClerk = NULL
		END
DECLARE @CEO NVARCHAR(100)
	if(@isCEO = 1)
		BEGIN
			SET @CEO = 'CEO'
		END
	ELSE
		BEGIN
			SET @CEO = NULL
		END
DECLARE @accounting NVARCHAR(100)
	if(@isAccounting = 1)
		BEGIN
			SET @accounting = 'Accounting'
		END
	ELSE
		BEGIN
			SET @accounting = NULL
		END
DECLARE @procurement NVARCHAR(100)
	if(@isProcurement = 1)
		BEGIN
			SET @procurement = 'Procurement'
		END
	ELSE
		BEGIN
			SET @procurement = NULL
		END

	--this is for the error table.  Never will have any errors
	SELECT
		NULL [errors]
		
	--selects all users and their status
	SELECT
		U.userID,
		U.firstName + ' ' + U.LastName [name],
		U.userName,
		U.pass,
		U.passwordHint,
		U.storeID
	FROM
		Users U
		INNER JOIN Statuses S ON S.statusID = U.statusID
		INNER JOIN UserRoles UR ON UR.userID = U.userID
		INNER JOIN Roles R ON R.roleID = UR.roleID
	WHERE
		R.RoleName IN (@admin, @generalManager, @storeManager, @storeKeeper, @subUnitClerk, @CEO, @accounting, @procurement)
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getUserByName')

DROP PROCEDURE sp_getUserByName

GO

--Created by Benjamin Strickler June 5 2012
CREATE PROCEDURE [dbo].[sp_getUserByName] (
	@FirstName	varchar(50),
	@LastName	varchar(50)
)
AS
BEGIN
	DECLARE @isUserID	int
	SET @isUserID = (SELECT U.UserID FROM Users U WHERE U.FirstName = @FirstName AND U.LastName = @LastName)
	
	if(@isUserID IS NOT NULL)
		BEGIN
			DECLARE 	@isAdmin BIT
			IF EXISTS (SELECT UserRoleID FROM UserRoles UR INNER JOIN Roles R ON R.roleID = UR.roleID WHERE UR.UserID = @isUserID AND UR.RoleID = 1)
				BEGIN
					SET @isAdmin = 1
				END

			DECLARE		@isGeneralManager BIT
			IF EXISTS (SELECT UserRoleID FROM UserRoles UR INNER JOIN Roles R ON R.roleID = UR.roleID WHERE UR.UserID = @isUserID AND UR.RoleID = 2)
				BEGIN
					SET @isGeneralManager = 1
				END
				
			DECLARE		@isStoreManager BIT
			IF EXISTS (SELECT UserRoleID FROM UserRoles UR INNER JOIN Roles R ON R.roleID = UR.roleID WHERE UR.UserID = @isUserID AND UR.RoleID = 3)
				BEGIN
					SET @isStoreManager = 1
				END 
				
			DECLARE		@isStoreKeeper BIT
			IF EXISTS (SELECT UserRoleID FROM UserRoles UR INNER JOIN Roles R ON R.roleID = UR.roleID WHERE UR.UserID = @isUserID AND UR.RoleID = 4)
				BEGIN
					SET @isStoreKeeper = 1
				END
				
			DECLARE		@isSubUnitClerk BIT
			IF EXISTS (SELECT UserRoleID FROM UserRoles UR INNER JOIN Roles R ON R.roleID = UR.roleID WHERE UR.UserID = @isUserID AND UR.RoleID = 5)
				BEGIN
					SET @isSubUnitClerk = 1
				END
				
			DECLARE		@isCEO BIT
			IF EXISTS (SELECT UserRoleID FROM UserRoles UR INNER JOIN Roles R ON R.roleID = UR.roleID WHERE UR.UserID = @isUserID AND UR.RoleID = 6)
				BEGIN
					SET @isCEO = 1
				END
				
			DECLARE		@isAccounting BIT
			IF EXISTS (SELECT UserRoleID FROM UserRoles UR INNER JOIN Roles R ON R.roleID = UR.roleID WHERE UR.UserID = @isUserID AND UR.RoleID = 7)
				BEGIN
					SET @isAccounting = 1
				END
				
			DECLARE		@isProcurement BIT
			IF EXISTS (SELECT UserRoleID FROM UserRoles UR INNER JOIN Roles R ON R.roleID = UR.roleID WHERE UR.UserID = @isUserID AND UR.RoleID = 8)
				BEGIN
					SET @isProcurement = 1
				END
			--this is for the error table.  Never will have any errors
			SELECT
				NULL [errors]
				
			--selects all users and their status
			SELECT
				U.userID,
				U.firstName,
				U.LastName,
				U.userName,
				U.pass,
				U.passwordHint,
				U.storeID,
				@isAdmin [Admin],
				@isGeneralManager [General Manager],
				@isStoreManager [Store Manager],
				@isStoreKeeper [Store Keeper],
				@isSubUnitClerk [Sub Unit Clerk],
				@isCEO [CEO],
				@isAccounting [Accounting],
				@isProcurement [Procurement]
			FROM
				Users U
			WHERE
				U.UserID = @isUserID
		END
	ELSE
		BEGIN
				--this is for the error table.  Never will have any errors
				SELECT
				'This user does not exist' [errors]
		END	
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getUserByID')

DROP PROCEDURE sp_getUserByID

GO

--Created by Benjamin Strickler June 5 2012
CREATE PROCEDURE [dbo].[sp_getUserByID] (
	@isUserID	int
)
AS
BEGIN

	DECLARE 	@isAdmin BIT
	IF EXISTS (SELECT UserRoleID FROM UserRoles UR INNER JOIN Roles R ON R.roleID = UR.roleID WHERE UR.UserID = @isUserID AND UR.RoleID = 1)
		BEGIN
			SET @isAdmin = 1
		END

	DECLARE		@isGeneralManager BIT
	IF EXISTS (SELECT UserRoleID FROM UserRoles UR INNER JOIN Roles R ON R.roleID = UR.roleID WHERE UR.UserID = @isUserID AND UR.RoleID = 2)
		BEGIN
			SET @isGeneralManager = 1
		END
		
	DECLARE		@isStoreManager BIT
	IF EXISTS (SELECT UserRoleID FROM UserRoles UR INNER JOIN Roles R ON R.roleID = UR.roleID WHERE UR.UserID = @isUserID AND UR.RoleID = 3)
		BEGIN
			SET @isStoreManager = 1
		END 
		
	DECLARE		@isStoreKeeper BIT
	IF EXISTS (SELECT UserRoleID FROM UserRoles UR INNER JOIN Roles R ON R.roleID = UR.roleID WHERE UR.UserID = @isUserID AND UR.RoleID = 4)
		BEGIN
			SET @isStoreKeeper = 1
		END
		
	DECLARE		@isSubUnitClerk BIT
	IF EXISTS (SELECT UserRoleID FROM UserRoles UR INNER JOIN Roles R ON R.roleID = UR.roleID WHERE UR.UserID = @isUserID AND UR.RoleID = 5)
		BEGIN
			SET @isSubUnitClerk = 1
		END
		
	DECLARE		@isCEO BIT
	IF EXISTS (SELECT UserRoleID FROM UserRoles UR INNER JOIN Roles R ON R.roleID = UR.roleID WHERE UR.UserID = @isUserID AND UR.RoleID = 6)
		BEGIN
			SET @isCEO = 1
		END
		
	DECLARE		@isAccounting BIT
	IF EXISTS (SELECT UserRoleID FROM UserRoles UR INNER JOIN Roles R ON R.roleID = UR.roleID WHERE UR.UserID = @isUserID AND UR.RoleID = 7)
		BEGIN
			SET @isAccounting = 1
		END
		
	DECLARE		@isProcurement BIT
	IF EXISTS (SELECT UserRoleID FROM UserRoles UR INNER JOIN Roles R ON R.roleID = UR.roleID WHERE UR.UserID = @isUserID AND UR.RoleID = 8)
		BEGIN
			SET @isProcurement = 1
		END
	

DECLARE @userID int
	if(@isUserID IS NOT NULL)
		BEGIN
			SET @userID = @isUserID
		END
	ELSE
		BEGIN
			SET @userID = NULL
		END

	--this is for the error table.  Never will have any errors
	SELECT
		NULL [errors]
		
	--selects all users and their status
	SELECT
		U.userID,
		U.firstName, 
		U.lastName,
		U.userName,
		U.pass,
		U.passwordHint,
		U.statusID,
		U.storeID,
		ISNULL(@isAdmin, 0) [Admin],
		ISNULL(@isGeneralManager, 0) [General Manager],
		ISNULL(@isStoreManager, 0) [Store Manager],
		ISNULL(@isStoreKeeper, 0) [Store Keeper],
		ISNULL(@isSubUnitClerk, 0) [Sub Unit Clerk],
		ISNULL(@isCEO, 0) [CEO],
		ISNULL(@isAccounting, 0) [Accounting],
		ISNULL(@isProcurement, 0) [Procurement]
	FROM
		Users U
	WHERE
		U.UserID = @userID
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getUnits')

DROP PROCEDURE sp_getUnits

GO

CREATE PROCEDURE [dbo].[sp_getUnits]
AS
BEGIN
	--This is the error table.  There should never be any errors so I will always return null
	SELECT
		NULL
	
	SELECT
		U.unitID,
		U.unitName
	FROM
		Units U
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getSubUnits')

DROP PROCEDURE sp_getSubUnits

GO

--Created by Michael Meyer June 25 2012
CREATE PROCEDURE [dbo].[sp_getSubUnits]

AS
BEGIN

DECLARE @results NVARCHAR(100)

IF EXISTS (
	SELECT TOP 1
		SU.subUnitID
	FROM
		SubUnits SU
	)
	BEGIN
		SET @results = null
		
		SELECT
			@results [ErrorMessage]
			
		SELECT
			SU.subUnitID,
			SU.subUnitName,
			S.statusName
		FROM
			subUnits SU
			INNER JOIN Statuses S ON S.statusID = SU.statusID
		--WHERE
		--	ST.statusID = @statusID
	END
	
	ELSE
	
	BEGIN
		SET @results = 'No Sub Units.'
		
		SELECT
			@results [ErrorMessage]
			
		SELECT
			NULL
	END
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getSubUnitByName')

DROP PROCEDURE sp_getSubUnitByName

GO

--Created by michael meyer june 19 2012
CREATE PROCEDURE [dbo].[sp_getSubUnitByName] (
	@subUnitName NVARCHAR(100)
)
AS
BEGIN
	DECLARE @results NVARCHAR(100)
	
	IF EXISTS(
		SELECT
			SU.subUnitID
		FROM
			SubUnits SU
		WHERE
			SU.subUnitName = @subUnitName
		)
	BEGIN
		SET @results = null
		
		SELECT
			@results [ErrorMessage]
		
		SELECT
			SU.subUnitID,
			SU.subUnitName,
			S.statusName
		FROM
			SubUnits SU
			INNER JOIN Statuses S ON S.statusID = SU.statusID
		WHERE
			SU.subUnitName = @subUnitName
	END
	ELSE
	BEGIN
		SET @results = 'Sub Unit does not exist by that name.'
		
		SELECT
			@results [ErrorMessage]
			
		SELECT
			NULL
	END	
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getSubUnitByID')

DROP PROCEDURE sp_getSubUnitByID

GO

--Created by Michael Meyer June 21, 2012
CREATE PROCEDURE [dbo].[sp_getSubUnitByID] (
	@subUnitID INT
)
AS
BEGIN
DECLARE @results NVARCHAR(100)
	IF NOT EXISTS(
			SELECT
				SU.subUnitID
			FROM
				SubUnits SU
			WHERE
				SU.subUnitId = @subUnitID
		)
	BEGIN
		SET @results = 'No sub unit exists by that id.'
		
		SELECT
			@results [ErrorMessage]
			
		SELECT
			NULL
	END
	
	ELSE
	
	BEGIN
		SET @results = null
		
		SELECT
			@results [ErrorMessage]
			
		SELECT
			SU.subUnitID,
			SU.subUnitName,
			SU.statusID
		FROM
			SubUnits SU
		WHERE
			SU.subUnitId = @subUnitID	
	END
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getStoreUnitStatus')

DROP PROCEDURE sp_getStoreUnitStatus

GO

--Created by Michael Meyer July 5 2012
CREATE PROCEDURE [dbo].[sp_getStoreUnitStatus] (
	@storeID INT,
	@subUnitID INT
)
AS
BEGIN

DECLARE @results NVARCHAR(100)

	IF NOT EXISTS (
		SELECT TOP 1
				SU.storeUnitID
			FROM
				storeUnits SU
			WHERE
				SU.storeID = @storeID
				AND SU.subUnitID = @subUnitID
		)
	BEGIN
		SET @results = 'No store Unit.'
		
		SELECT
			@results
			
		SELECT
			NULL
	END	
	ELSE
	BEGIN
		
		SET @results = NULL
		
		SELECT
			@results
			
		SELECT
			SU.statusID
		FROM
			storeUnits SU
		WHERE
			SU.storeID = @storeID
			AND SU.subUnitID = @subUnitID
	END
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getStoreUnits')

DROP PROCEDURE sp_getStoreUnits

GO

--Created by Michael Meyer June 25 2012
CREATE PROCEDURE [dbo].[sp_getStoreUnits]

AS
BEGIN

DECLARE @results NVARCHAR(100)

IF EXISTS (
	SELECT TOP 1
		SU.storeUnitID
	FROM
		storeUnits SU
	)
	BEGIN
		SET @results = null
		
		SELECT
			@results [ErrorMessage]
			
		SELECT
			S.storeID,
			S.storeName,
			SUN.subUnitID,
			SUN.subUnitName,
			ST.statusName
		FROM
			StoreUnits SU
			INNER JOIN Stores S ON S.storeID = SU.storeID
			INNER JOIN subUnits SUN ON SUN.subUnitID = SU.subUnitID
			INNER JOIN Statuses ST ON ST.statusID = SU.statusID
		--WHERE
		--	ST.statusID = @statusID
	END
	
	ELSE
	
	BEGIN
		SET @results = 'No Sub Units.'
		
		SELECT
			@results [ErrorMessage]
			
		SELECT
			NULL
	END
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getStoreUnitItemQty')

DROP PROCEDURE sp_getStoreUnitItemQty

GO

--Created by Preston Berrett July 19, 2012
CREATE PROCEDURE [dbo].[sp_getStoreUnitItemQty]
	@StoreID INT,
	@SubUnitID INT,
	@ItemID INT
AS
BEGIN
DECLARE @results NVARCHAR(100)
DECLARE @UnitName NVARCHAR(100)
SET @UnitName = (SELECT StoreName FROM Stores WHERE StoreID = @SubUnitID)
	IF NOT EXISTS (
		SELECT
			*
		FROM
			StoreUnits SU
			JOIN SubUnits SUB ON SUB.SubUnitID = SU.SubUnitID
			JOIN StoreUnitItems SUI ON SUI.StoreUnitID = SU.StoreUnitID
		WHERE
			SU.StoreID = @StoreID
			AND 
			SUB.SubUnitName = @UnitName
			AND 
			SUI.ItemID = @ItemID
	)
	BEGIN -- if there is no requisition with that item
		SET @results = 'That store and subunit with that unit do not exist';
		
		SELECT
			@results;
	END
	ELSE
	BEGIN

		SET @results = NULL;
		
		SELECT
			@results;
			
		SELECT
			SUI.StoreUnitQty
		FROM
			StoreUnits SU
			JOIN SubUnits SUB ON SUB.SubUnitID = SU.SubUnitID
			JOIN StoreUnitItems SUI ON SUI.StoreUnitID = SU.StoreUnitID
		WHERE
			SU.StoreID = @StoreID
			AND 
			SUB.SubUnitName = @UnitName
			AND 
			SUI.ItemID = @ItemID
			
	END
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getStoreTypes')

DROP PROCEDURE sp_getStoreTypes

GO

--Created by Michael Meyer June 23 2012
CREATE PROCEDURE [dbo].[sp_getStoreTypes]

AS
BEGIN

DECLARE @results NVARCHAR(100)

--DECLARE @statusID INT
--SET @statusID = (
--	SELECT TOP 1
--		S.statusID
--	FROM
--		StoreTypes ST
--		INNER JOIN Statuses S ON S.statusID = ST.statusID
--			AND S.statusName = 'Active'
--	)
	
IF EXISTS (
	SELECT TOP 1
		*
	FROM
		StoreTypes ST
	--WHERE
	--	ST.statusID = @statusID
	)
	BEGIN
		SET @results = null
		
		SELECT
			@results [ErrorMessage]
			
		SELECT
			ST.storeTypeID,
			ST.storeTypeName,
			S.statusName
		FROM
			StoreTypes ST
			INNER JOIN Statuses S ON S.statusID = ST.statusID
		--WHERE
		--	ST.statusID = @statusID
	END
	
	ELSE
	
	BEGIN
		SET @results = 'No store types.'
		
		SELECT
			@results [ErrorMessage]
			
		SELECT
			NULL
	END
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getStoresByType')

DROP PROCEDURE sp_getStoresByType

GO

--Created by Benjamin Strickler 08/02/2012
CREATE PROCEDURE [dbo].[sp_getStoresByType]
	@type	int
AS
BEGIN
	
	DECLARE @results NVARCHAR(100)
	BEGIN
		SET @results = NULL
		
		SELECT
			@results [ErrorMessage]
		
		SELECT
			ST.storeID,
			ST.storeName
		FROM
			Stores ST
		WHERE
			ST.StatusID = 50
			AND ST.StoreTypeID = @type
	END
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getStores')

DROP PROCEDURE sp_getStores

GO

--Created by Michael Meyer July 5 2012
CREATE PROCEDURE [dbo].[sp_getStores]
AS
BEGIN
	
	DECLARE @results NVARCHAR(100)

	IF NOT EXISTS(
		SELECT
			ST.storeID
		FROM
			Stores ST
	)
	BEGIN
		SET @results = 'There are no stores.'
		
		SELECT
			@results [ErrorMessage]
			
		SELECT
			NULL
	END
	ELSE
	BEGIN
		SET @results = NULL
		
		SELECT
			@results [ErrorMessage]
		
		SELECT
			ST.storeID,
			ST.storeName,
			ST.statusID
		FROM
			Stores ST
	END
END

GO

-- GET Vendors
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getVendors')

DROP PROCEDURE sp_getVendors

GO

--Created by Michael Meyer July 5 2012
CREATE PROCEDURE [dbo].[sp_getVendors]
AS
BEGIN
	
	DECLARE @results NVARCHAR(100)

	IF NOT EXISTS(
		SELECT
			V.VendorID
		FROM
			Vendors V
	)
	BEGIN
		SET @results = 'There are no vendors.'
		
		SELECT
			@results [ErrorMessage]
			
		SELECT
			NULL
	END
	ELSE
	BEGIN
		SET @results = NULL
		
		SELECT
			@results [ErrorMessage]
		
		SELECT
			V.VendorName,
			V.VendorAddress,
			V.PhoneNumber,
			V.VendorID,
			S.StatusName
		FROM
			Vendors V
			JOIN Statuses S ON (V.StatusID = S.StatusID)
	END
END

GO


IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getStoreByName')

DROP PROCEDURE sp_getStoreByName

GO

--Created by Michael Meyer June 18 2012
CREATE PROCEDURE [dbo].[sp_getStoreByName] (
	@storeName NVARCHAR(100)
)
AS
BEGIN
	
	DECLARE @results NVARCHAR(100)

	IF NOT EXISTS(
		SELECT
			ST.storeID
		FROM
			Stores ST
		WHERE
			ST.storeName = @storeName
	)
	BEGIN
		SET @results = 'There is no store by that name.'
		
		SELECT
			@results [ErrorMessage]
			
		SELECT
			NULL
	END
	ELSE
	BEGIN
		SET @results = NULL
		
		SELECT
			@results [ErrorMessage]
		
		SELECT
			ST.storeID,
			ST.storeName,
			S.StatusName
		FROM
			Stores ST
			INNER JOIN Statuses S on S.statusID = ST.statusID
		WHERE
			ST.storeName = @storeName			
	END
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getStoreByID')

DROP PROCEDURE sp_getStoreByID

GO

--Created by Michael Meyer June 18 2012
CREATE PROCEDURE [dbo].[sp_getStoreByID] (
	@storeID NVARCHAR(100)
)
AS
BEGIN
	
	DECLARE @results NVARCHAR(100)

	IF NOT EXISTS(
		SELECT
			ST.storeID
		FROM
			Stores ST
		WHERE
			ST.storeID = @storeID
	)
	BEGIN
		SET @results = 'There is no store for that ID.'
		
		SELECT
			@results [ErrorMessage]
			
		SELECT
			NULL
	END
	ELSE
	BEGIN
		SET @results = NULL
		
		SELECT
			@results [ErrorMessage]
		
		SELECT
			ST.storeID,
			ST.storeName,
			ST.storeTypeID,
			ST.statusID
		FROM
			Stores ST
		WHERE
			ST.storeID = @storeID			
	END
END

GO

--IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getSRA')

--DROP PROCEDURE sp_getSRA

--GO

----Created by Preston Berrett June 26, 2012
--CREATE PROCEDURE [dbo].[sp_getSRA]
--	@RequisitionID INT
--AS
--BEGIN
--DECLARE @results NVARCHAR(100)
--	IF NOT EXISTS (
--		SELECT
--			R.RequisitionID
--		FROM
--			Requisitions R
--		WHERE
--			R.RequisitionID = @RequisitionID
--	)
--	BEGIN --if there aren't any pending requisitions
--		SET @results = 'FALSE';
		
--		SELECT
--			@results;
--	END
--	ELSE
--	BEGIN
		
--		SET @results = NULL;
--		SELECT
--			@results;
			
--		SELECT
--			SRA1,
--			SRA2,
--			SRA3,
--			SRA4
--		FROM
--			Requisitions;
		
--	END
--END

--GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getSaltByUserName')

DROP PROCEDURE sp_getSaltByUserName

GO

--created by michael meyer July 28, 2012

CREATE PROCEDURE [dbo].[sp_getSaltByUserName] (
	@userName NVARCHAR(100)
)
AS
BEGIN
DECLARE @results NVARCHAR(100)
	IF EXISTS (
		SELECT
			U.userID
		FROM
			users U
		WHERE
			U.userName = @userName
	)
	BEGIN
		SET @results = NULL
	END
	ELSE
	BEGIN
		SET @results = 'Not a valid username.'
	END
	
	SELECT
		@results [errorMessage]
		
	SELECT
		U.salt
	FROM
		users U
	WHERE
		U.userName = @userName
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getRequisitionsNotClosedByStore')

DROP PROCEDURE sp_getRequisitionsNotClosedByStore

GO

CREATE PROCEDURE [dbo].[sp_getRequisitionsNotClosedByStore]
@StoreID	int

AS
BEGIN
DECLARE @results NVARCHAR(100)
	IF NOT EXISTS (
		SELECT
			R.RequisitionID,
			R.RequisitionName,
			R.StoreID,
			R.orderDate,
			S.StatusName
		FROM
			Requisitions R
			JOIN RequisitionItems RI ON RI.RequisitionID = R.RequisitionID
			JOIN Statuses S ON S.statusID = R.statusID
		WHERE
			(S.StatusName != 'Closed' AND S.StatusName != 'CreatingOutside') OR
			R.DenialComments != NULL OR
			R.DenialComments != '' AND
			R.StoreID = @StoreID
	)
	BEGIN --if there are not any requisitions that are not closed
		SET @results = 'No Requisitions';
		
		SELECT
			@results;
	END
	ELSE
	BEGIN
		
		SET @results = NULL;
		
		SELECT
			@results;
			
		SELECT
			R.RequisitionID,
			R.RequisitionName,
			R.StoreID,
			R.orderDate,
			S.StatusName
		FROM
			Requisitions R
			JOIN Statuses S ON S.statusID = R.statusID
		WHERE
			(R.StatusID != 41 AND R.StatusID != 46) OR
			R.DenialComments != NULL OR
			R.DenialComments != '' AND
			R.StoreID = @StoreID
	END
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getRequisitionsNotClosed')

DROP PROCEDURE sp_getRequisitionsNotClosed

GO

--Created by Preston Berrett June 19, 2012
CREATE PROCEDURE [dbo].[sp_getRequisitionsNotClosed]
AS
BEGIN
DECLARE @results NVARCHAR(100)
	IF NOT EXISTS (
		SELECT
			R.RequisitionID,
			R.RequisitionName,
			R.StoreID,
			R.orderDate,
			S.StatusName
		FROM
			Requisitions R
			JOIN RequisitionItems RI ON RI.RequisitionID = R.RequisitionID
			JOIN Statuses S ON S.statusID = R.statusID
		WHERE
			S.StatusName != 'Closed'
	)
	BEGIN --if there are not any requisitions that are not closed
		SET @results = 'No Requisitions';
		
		SELECT
			@results;
	END
	ELSE
	BEGIN
		
		SET @results = NULL;
		
		SELECT
			@results;
			
		SELECT
			R.RequisitionID,
			R.RequisitionName,
			R.StoreID,
			R.orderDate,
			S.StatusName
		FROM
			Requisitions R
			JOIN Statuses S ON S.statusID = R.statusID
		WHERE
			R.StatusID != 41
	END
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getRequisitionsByPendingStatus')

DROP PROCEDURE sp_getRequisitionsByPendingStatus

GO

--Created by Preston Berrett June 19, 2012
CREATE PROCEDURE [dbo].[sp_getRequisitionsByPendingStatus]
AS
BEGIN
DECLARE @results NVARCHAR(100)
	IF NOT EXISTS (
		SELECT
			RequisitionID,
			RequisitionName,
			orderDate,
			S.StatusName
		FROM
			Requisitions R
			LEFT JOIN Statuses S ON S.statusID = R.statusID
		WHERE
			S.StatusName = 'Pending'
	)
	BEGIN --if there aren't any pending requisitions
		SET @results = 'No Pending Requisitions';
		
		SELECT
			@results;
	END
	ELSE
	BEGIN
		
		SET @results = NULL
		
		SELECT
			@results;
			
		SELECT
			RequisitionID,
			RequisitionName,
			orderDate,
			st.StoreName,
			S.StatusName
		FROM
			Requisitions R
			JOIN Stores st ON st.StoreID = R.StoreID
			LEFT JOIN Statuses S ON S.statusID = R.statusID
		WHERE
			S.StatusName = 'Pending' /* Requisitions */
	END
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getRequisitionsByOrderedStatus')

DROP PROCEDURE sp_getRequisitionsByOrderedStatus

GO

--Created by Preston Berrett June 19, 2012
CREATE PROCEDURE [dbo].[sp_getRequisitionsByOrderedStatus]
	@StoreID INT
AS
BEGIN
DECLARE @results NVARCHAR(100)
	IF NOT EXISTS (
		SELECT
			R.RequisitionID,
			R.RequisitionName,
			R.orderDate,
			S.StatusName
		FROM
			Requisitions R
			INNER JOIN RequisitionItems RI ON RI.RequisitionID = R.RequisitionID
			LEFT JOIN Statuses S ON S.statusID = R.statusID
		WHERE
			S.StatusName = 'Ordered'
			AND 
			R.StoreID = @StoreID	
	)
	BEGIN --if there aren't any pending requisitions
		SET @results = 'No Requisitions';
		
		SELECT
			@results;
	END
	ELSE
	BEGIN
		
		SET @results = NULL;
		
		SELECT
			@results;
			
		SELECT
			R.RequisitionID,
			R.RequisitionName,
			R.orderDate,
			S.StatusName
		FROM
			Requisitions R
			INNER JOIN RequisitionItems RI ON RI.RequisitionID = R.RequisitionID
			LEFT JOIN Statuses S ON S.statusID = R.statusID
		WHERE
			S.StatusName = 'Ordered'
			AND 
			R.StoreID = @StoreID
	END
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getRequisitionsByGMStatus')

DROP PROCEDURE sp_getRequisitionsByGMStatus

GO

--Created by Preston Berrett June 19, 2012
CREATE PROCEDURE [dbo].[sp_getRequisitionsByGMStatus]
AS
BEGIN
DECLARE @results NVARCHAR(100)
	IF NOT EXISTS (
		SELECT
			RequisitionID,
			RequisitionName,
			orderDate,
			S.StatusName
		FROM
			Requisitions R
			LEFT JOIN Statuses S ON S.statusID = R.statusID
		WHERE
			S.StatusName = 'Approved By GM'
	)
	BEGIN --if there aren't any pending requisitions
		SET @results = 'No Pending Requisitions';
		
		SELECT
			@results;
	END
	ELSE
	BEGIN
		
		SET @results = NULL;
		
		SELECT
			@results;
			
		SELECT
			RequisitionID,
			RequisitionName,
			orderDate,
			st.StoreName,
			S.StatusName
		FROM
			Requisitions R
			JOIN Stores st ON st.StoreID = R.StoreID
			LEFT JOIN Statuses S ON S.statusID = R.statusID
		WHERE
			S.StatusName = 'Approved By GM' /* Requisitions */
	END
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getRequisitionsByCEOStatus')

DROP PROCEDURE sp_getRequisitionsByCEOStatus

GO

--Created by Preston Berrett June 19, 2012
CREATE PROCEDURE [dbo].[sp_getRequisitionsByCEOStatus]
AS
BEGIN
DECLARE @results NVARCHAR(100)
	IF NOT EXISTS (
		SELECT
			RequisitionID,
			RequisitionName,
			orderDate,
			S.StatusName
		FROM
			Requisitions R
			LEFT JOIN Statuses S ON S.statusID = R.statusID
		WHERE
			S.StatusName = 'Approved By CEO'
	)
	BEGIN --if there aren't any pending requisitions
		SET @results = 'No Requisitions';
		
		SELECT
			@results;
	END
	ELSE
	BEGIN
		
		SET @results = NULL;
		
		SELECT
			@results;
			
		SELECT
			RequisitionID,
			RequisitionName,
			orderDate,
			S.StatusName
		FROM
			Requisitions R
			LEFT JOIN Statuses S ON S.statusID = R.statusID
		WHERE
			S.StatusName = 'Approved By CEO' /* Requisitions */
	END
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getRequisitionItemsByRequisitionID')

DROP PROCEDURE sp_getRequisitionItemsByRequisitionID

GO

--Created by Preston Berrett June 19, 2012
CREATE PROCEDURE [dbo].[sp_getRequisitionItemsByRequisitionID]
	@RequisitionID INT,
	@getDenied	BIT
AS
BEGIN
DECLARE @results NVARCHAR(100)
	IF NOT EXISTS (
		SELECT
			R.RequisitionID,
			RI.ItemID,
			I.ItemName,
			S.StatusName,
			RI.RequisitionQty
		FROM
			Requisitions R
			INNER JOIN RequisitionItems RI ON RI.RequisitionID = R.RequisitionID
			INNER JOIN Items I ON I.ItemID = RI.ItemID
			LEFT JOIN Statuses S ON S.statusID = RI.statusID
		WHERE
			R.RequisitionID = @RequisitionID	
	)
	BEGIN --if there aren't any pending requisitions
		SET @results = 'No Requisitions';
		
		SELECT
			@results;
	END
	ELSE
	BEGIN
		
		SET @results = NULL;
		
		SELECT
			@results;
			
		IF(@getDenied = 1)
		BEGIN
			SELECT
				RI.ItemID,
				I.ItemName,
				S.StatusName,
				R.StoreID,
				RI.RequisitionQty,
				RI.Denied,
				(SELECT ((SELECT sum(Inv.TransactionQty) FROM Inventory Inv WHERE Inv.ItemID = RI.ItemID) - sum(H.HoldQty)) FROM Holds H WHERE H.ItemID = RI.ItemID) AS [TotalQuantity],
				ISNULL(X.totalQuantity, 0) [ReceivedQty],
				sto.SubUnitName,
				sto.SubUnitID,
				(SELECT TOP 1 VC.VendorCost FROM VendorCosts VC WHERE VC.ItemID = I.ItemID ORDER BY VC.VendorCost DESC) AS [avgCost]
			FROM
				Requisitions R
				INNER JOIN RequisitionItems RI ON RI.RequisitionID = R.RequisitionID
				INNER JOIN Items I ON I.ItemID = RI.ItemID
				LEFT JOIN (
					SELECT
						RRI.RequisitionItemID,
						SUM(RRI.QtyReceived) [totalQuantity]
					FROM
						RequisitionReceivedItems RRI
					GROUP BY
						RRI.RequisitionItemID
				) X ON X.requisitionItemID = RI.RequisitionItemID
				LEFT JOIN RequisitionReceivedItems RRI ON RRI.RequisitionItemID = RI.RequisitionItemID
				LEFT JOIN Statuses S ON S.statusID = RI.statusID
				JOIN SubUnits sto on R.StoreID = sto.SubUnitID
			WHERE
				R.RequisitionID = @RequisitionID
			GROUP BY
				RI.ItemID,
				I.ItemName,
				S.StatusName,
				RI.RequisitionQty,
				R.StoreID,
				RI.Denied,
				[totalQuantity],
				ISNULL(X.totalQuantity, 0),
				sto.SubUnitName,
				sto.SubUnitID,
				I.ItemID
			ORDER BY
				sto.SubUnitID,
				RI.ItemID
		END
		ELSE
		BEGIN
			SELECT
				RI.ItemID,
				I.ItemName,
				S.StatusName,
				RI.RequisitionQty,
				R.StoreID,
				(SELECT ((SELECT sum(Inv.TransactionQty) FROM Inventory Inv WHERE Inv.ItemID = RI.ItemID) - sum(H.HoldQty)) FROM Holds H WHERE H.ItemID = RI.ItemID) AS [TotalQuantity],
				ISNULL(X.totalQuantity, 0) [ReceivedQty],
				sto.SubUnitName,
				sto.SubUnitID,
				(SELECT TOP 1 VC.VendorCost FROM VendorCosts VC WHERE VC.ItemID = I.ItemID ORDER BY VC.VendorCost DESC) AS [avgCost]
			FROM
				Requisitions R
				INNER JOIN RequisitionItems RI ON RI.RequisitionID = R.RequisitionID
				INNER JOIN Items I ON I.ItemID = RI.ItemID
				LEFT JOIN (
					SELECT
						RRI.RequisitionItemID,
						SUM(QtyReceived) [totalQuantity]
					FROM
						RequisitionReceivedItems RRI
					GROUP BY
						RRI.RequisitionItemID
				) X ON X.requisitionItemID = RI.RequisitionItemID
				LEFT JOIN RequisitionReceivedItems RRI ON RRI.RequisitionItemID = RI.RequisitionItemID
				LEFT JOIN Statuses S ON S.statusID = RI.statusID
				JOIN SubUnits sto on R.StoreID = sto.SubUnitID
			WHERE
				R.RequisitionID = @RequisitionID AND
				RI.Denied = 0
			GROUP BY
				RI.ItemID,
				I.ItemName,
				S.StatusName,
				RI.RequisitionQty,
				R.StoreID,
				[totalQuantity],
				ISNULL(X.totalQuantity, 0),
				sto.SubUnitName,
				sto.SubUnitID,
				I.ItemID
			ORDER BY
				sto.SubUnitID,
				RI.ItemID
		END
	END
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_GetRequestsByStoreID')

DROP PROCEDURE sp_GetRequestsByStoreID

GO

CREATE PROCEDURE [dbo].[sp_GetRequestsByStoreID]
	@StoreID		int
AS
BEGIN
	DECLARE @results NVARCHAR(100)
	IF NOT EXISTS (SELECT * FROM SatelliteStoreRequests WHERE StoreID = @StoreID)
	BEGIN	
		SET @results = 'Currently No Request';
		SELECT @results;
	END
	ELSE
	BEGIN
		SET @results = NULL;
		SELECT @results;
	
		SELECT
			ssr.SatelliteStoreRequestID,
			ssr.RequestDate,
			ssr.RequestDescription,
			s.StatusName
		FROM
			SatelliteStoreRequests ssr
			JOIN Statuses s on s.StatusID = ssr.StatusID
		WHERE
			ssr.StoreID = @StoreID
			AND s.StatusID != 81
	END
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getRequestReadyItems')

DROP PROCEDURE sp_getRequestReadyItems

GO

--Created by Preston Berrett July 26, 2012
CREATE PROCEDURE [dbo].[sp_getRequestReadyItems]
	@RequestID INT
AS
BEGIN
DECLARE @results NVARCHAR(100)
	IF NOT EXISTS (
		SELECT
			SSR.SatelliteStoreRequestID
		FROM
			SatelliteStoreRequests SSR
		WHERE
			SSR.SatelliteStoreRequestID = @RequestID	
	)
	BEGIN --if there aren't any store requests
		SET @results = 'No Store Requests';
		
		SELECT
			@results;
	END
	ELSE
	BEGIN
		SET @results = NULL
		
		SELECT
			@results
			
		SELECT
			SRI.StoreRequestItemID,
			SRI.ItemID,
			I.ItemName,
			S.StatusName,
			SRI.QtyRequested,
			SUI.StoreUnitQty StoreUnitQuantity,
			(SELECT ((SELECT sum(Inv.TransactionQty) FROM Inventory Inv WHERE Inv.ItemID = SRI.ItemID) - sum(H.HoldQty)) FROM Holds H WHERE H.ItemID = SRI.ItemID) AS [TotalQuantity]
		FROM
			SatelliteStoreRequests SSR
			INNER JOIN StoreRequestItems SRI ON SRI.SatelliteStoreRequestID = SSR.SatelliteStoreRequestID
			INNER JOIN Statuses S ON S.StatusID = SSR.StatusID
			INNER JOIN Items I ON I.ItemID = SRI.ItemID
			LEFT JOIN StoreUnits SU ON SU.StoreID = SRI.SatelliteStoreRequestID
			LEFT JOIN StoreUnitItems SUI ON SUI.ItemID = I.ItemID
				AND SUI.StoreUnitID = SU.StoreUnitID
		WHERE
			SSR.SatelliteStoreRequestID = @RequestID
			AND 
			SSR.StatusID = 82
			
	END
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getRequestReady')

DROP PROCEDURE sp_getRequestReady

GO

--Created by Preston Berrett July 26, 2012
CREATE PROCEDURE [dbo].[sp_getRequestReady]
	@StoreID INT
AS
BEGIN
DECLARE @results NVARCHAR(100)
	IF NOT EXISTS (
		SELECT
			*
		FROM
			SatelliteStoreRequests SSR
			FULL JOIN StoreRequestItems SRI on SSR.SatelliteStoreRequestID = SRI.SatelliteStoreRequestID
			FULL JOIN Items I on I.ItemID = SRI.ItemID
			FULL JOIN ItemCategories IC ON I.ItemCategoryID = IC.ItemCategoryID
		WHERE
			IC.StoreID = @StoreID
			AND SRI.StatusID = 92
	)
	BEGIN --if there aren't any store requests
		SET @results = 'No Store Requests';
		
		SELECT
			@results;
	END
	ELSE
	BEGIN
		SET @results = NULL
		
		SELECT
			@results
			
		SELECT DISTINCT
			SSR.SatelliteStoreRequestID,
			SSR.RequestDescription,
			SSR.RequestDate,
			SSR.StoreID,
			st.StoreName
		FROM
			SatelliteStoreRequests SSR
			FULL JOIN StoreRequestItems SRI on SSR.SatelliteStoreRequestID = SRI.SatelliteStoreRequestID
			FULL JOIN Items I on I.ItemID = SRI.ItemID
			JOIN Stores st ON st.StoreID = SSR.StoreID
			JOIN ItemCategories IC ON I.ItemCategoryID = IC.ItemCategoryID
		WHERE
			IC.StoreID = @StoreID
			AND SRI.StatusID = 92
			
	END
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_GetRequestItemsByRequestID')

DROP PROCEDURE sp_GetRequestItemsByRequestID

GO

CREATE PROCEDURE [dbo].[sp_GetRequestItemsByRequestID]
	@RequestID				int,
	@StoreID				int
AS

BEGIN
	DECLARE @results NVARCHAR(100)
	DECLARE @subID	int
	SET @subID = (SELECT SubUnitID FROM Subunits su
					JOIN Stores st on st.StoreName = su.SubUnitName
					WHERE
						st.StoreID = @StoreID
						) 
	IF NOT EXISTS (SELECT * FROM StoreRequestItems WHERE SatelliteStoreRequestID = @RequestID)
	BEGIN
		SET @results = 'No Items For Request'
			
		SELECT @results;
	END
	ELSE
	BEGIN
		SET @results = NULL
		SELECT @results;
		
		SELECT DISTINCT
			ssr.SatelliteStoreRequestID,
			ssr.ItemID,
			i.ItemName,
			s.StatusName,
			ssr.QtyRequested,
			sui.StoreUnitQty,
			(SELECT ((SELECT sum(Inv.TransactionQty) FROM Inventory Inv WHERE Inv.ItemID = I.ItemID) - sum(H.HoldQty)) FROM Holds H WHERE H.ItemID = I.ItemID) AS [TotalQuantity],
			ISNULL(ssr.Allocated,0) as Allocated,
			ISNULL(t1.QtyReceived,0) as Received,
			(SELECT TOP 1 VW.MarkupCost FROM vw_getCosts VW WHERE VW.ItemID = I.ItemID GROUP BY VW.ItemID, VW.MarkupCost ORDER BY VW.MarkupCost DESC) AS [markupCost]
		FROM
			StoreRequestItems ssr
			LEFT JOIN Items i on i.ItemID = ssr.ItemID
			LEFT JOIN StoreUnitItems sui on sui.ItemID = i.ItemID
			LEFT JOIN StoreUnits su on su.StoreUnitID = sui.StoreUnitID
			LEFT JOIN Statuses s on s.StatusID = ssr.StatusID
			LEFT JOIN (SELECT SUM(rri.QtyReceived) AS 'QtyReceived', rri.StoreRequestItemID
						FROM RequestReceivedItems rri
						JOIN StoreRequestItems sri on sri.StoreRequestItemID = rri.StoreRequestItemID
						GROUP BY rri.StoreRequestItemID)t1 on ssr.StoreRequestItemID = t1.StoreRequestItemID 
		
		WHERE
			ssr.SatelliteStoreRequestID = @RequestID
			
			--AND su.SubUnitID = @SubID

	END
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_GetRequestItemsByRequestByStore')

DROP PROCEDURE sp_GetRequestItemsByRequestByStore

GO

CREATE PROCEDURE [dbo].[sp_GetRequestItemsByRequestByStore]
	@RequestID				int,
	@StoreID				int
AS

BEGIN
	DECLARE @results NVARCHAR(100)
	DECLARE @UnitName NVARCHAR(100)
	SET @UnitName = (SELECT s.StoreName
						FROM SatelliteStoreRequests sri 
						JOIN Stores s on sri.StoreID = s.StoreID 
						WHERE SatelliteStoreRequestID = @RequestID)	
					
	IF NOT EXISTS (SELECT * FROM StoreRequestItems WHERE SatelliteStoreRequestID = @RequestID)
	BEGIN
		SET @results = 'No Items For Request'
			
		SELECT @results;
	END
	ELSE
	BEGIN
		SET @results = NULL
		SELECT @results;

		DECLARE @Allocated int
		SET @Allocated = (SELECT TOP 1 SUM(ssr.Allocated)
								FROM StoreRequestItems ssr 
								JOIN Items i on i.ItemID = ssr.ItemID
								JOIN (SELECT sui.StoreUnitQty, sui.ItemID, su.StoreID, su.SubUnitID, su.StoreUnitID 
									FROM StoreUnitItems sui
									JOIN StoreUnits su ON su.StoreUnitID = sui.StoreUnitID
									WHERE su.StoreID = @StoreID
									AND su.SubUnitID = @StoreID) t1 ON t1.ItemID = i.ItemID 
									GROUP BY t1.ItemID)
		
		IF @Allocated IS NOT NULL
		BEGIN
		SELECT 
			ssr.SatelliteStoreRequestID,
			ssr.ItemID,
			i.ItemName,
			s.StatusName,
			ssr.QtyRequested,
			ssr.Allocated,
			t1.StoreUnitQty,
			(SELECT ((SELECT sum(Inv.TransactionQty) FROM Inventory Inv WHERE Inv.ItemID = ssr.ItemID) - sum(H.HoldQty)) FROM Holds H WHERE H.ItemID = ssr.ItemID) AS [TotalQuantity],
			IC.StoreID,
			i.EmergencyQty,
			ssr.StoreRequestItemID
		FROM
			StoreRequestItems ssr 
			JOIN Items i on i.ItemID = ssr.ItemID
			JOIN ItemCategories IC ON I.ItemCategoryID = IC.ItemCategoryID
			JOIN Statuses s on s.StatusID = ssr.StatusID
			JOIN (SELECT sui.StoreUnitQty, sui.ItemID, su.StoreID, su.SubUnitID, su.StoreUnitID 
						FROM StoreUnitItems sui
						JOIN StoreUnits su ON su.StoreUnitID = sui.StoreUnitID
						WHERE su.StoreID = @StoreID
						AND su.SubUnitID = @StoreID) t1 ON t1.ItemID = i.ItemID
			
		WHERE
			ssr.SatelliteStoreRequestID = @RequestID
			AND IC.StoreID = @StoreID
		END
		ELSE
		BEGIN
		SELECT 
			ssr.SatelliteStoreRequestID,
			ssr.ItemID,
			i.ItemName,
			s.StatusName,
			ssr.QtyRequested,
			t1.StoreUnitQty,
			(SELECT ((SELECT sum(Inv.TransactionQty) FROM Inventory Inv WHERE Inv.ItemID = ssr.ItemID) - sum(H.HoldQty)) FROM Holds H WHERE H.ItemID = ssr.ItemID) AS [TotalQuantity],
			IC.StoreID,
			i.EmergencyQty,
			ssr.StoreRequestItemID
		FROM
			StoreRequestItems ssr 
			JOIN Items i on i.ItemID = ssr.ItemID
			JOIN ItemCategories IC ON I.ItemCategoryID = IC.ItemCategoryID
			JOIN Statuses s on s.StatusID = ssr.StatusID
			JOIN (SELECT sui.StoreUnitQty, sui.ItemID, su.StoreID, su.SubUnitID, su.StoreUnitID 
						FROM StoreUnitItems sui
						JOIN StoreUnits su ON su.StoreUnitID = sui.StoreUnitID
						WHERE su.StoreID = @StoreID
						AND su.SubUnitID = @StoreID) t1 ON t1.ItemID = i.ItemID
			
		WHERE
			ssr.SatelliteStoreRequestID = @RequestID
			AND IC.StoreID = @StoreID
		END
	END
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getReceiptVoucher')

DROP PROCEDURE sp_getReceiptVoucher

GO

--Created by Preston Berrett July 26, 2012
CREATE PROCEDURE [dbo].[sp_getReceiptVoucher]
	@RequisitionID INT
AS
BEGIN
DECLARE @results NVARCHAR(100)
	IF NOT EXISTS (
		SELECT
			RI.RequisitionItemID
		FROM
			RequisitionItems RI
		WHERE
			RI.RequisitionID = @RequisitionID
	)
	BEGIN --if there aren't any store requests
		SET @results = 'No Requisitions';
		
		SELECT
			@results;
	END
	ELSE
	BEGIN
		SET @results = NULL;
		
		SELECT
			@results
			
		SELECT
			i.ItemName,
			ri.ItemID,
			VC.VendorCost,
			VC.VendorID,
			t1.ReceivingOfficer,
			--Convert(varchar(25),i.MarkupCost) as 'MarkupCost',
			t1.Quantity as 'QtyReceived'--,
			--Convert(varchar(25),Convert(decimal(10,2),Convert(Money, ISNULL((t1.Quantity * (i.MarkupCost*0.85)), 0)))) as 'ItemAmount',
			--Convert(varchar(25),Convert(decimal(10,2),Convert(Money, ISNULL((i.MarkupCost*0.85), 0)))) as 'ItemRate'
		FROM
			RequisitionItems ri 
		FULL JOIN	(SELECT
						ri.ItemID,
						ISNULL(SUM(rri.QtyReceived), 0) as 'Quantity',
						rri.ReceivingOfficer
					FROM 
						RequisitionItems ri
					JOIN RequisitionReceivedItems rri on ri.RequisitionItemID = rri.RequisitionItemID
					WHERE RequisitionID = @RequisitionID
					GROUP BY
						ri.ItemID, rri.ReceivingOfficer) AS t1 on ri.ItemID = t1.ItemID
		JOIN Items i on i.ItemID = ri.ItemID
		JOIN VendorCosts VC ON VC.ItemID = I.ItemID
		WHERE
			ri.RequisitionID = @RequisitionID
			AND t1.Quantity != 0
	END
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getPendingRequests')

DROP PROCEDURE sp_getPendingRequests

GO

--Created by Preston Berrett June 19, 2012
--Modified by Bryan Nixon Aug 17, 2012
CREATE PROCEDURE [dbo].[sp_getPendingRequests]
	@StoreID	int
AS
BEGIN
DECLARE @results NVARCHAR(100)
	IF NOT EXISTS (
		SELECT
			SSR.SatelliteStoreRequestID,
			SSR.RequestDate
		FROM
			SatelliteStoreRequests SSR
			JOIN StoreRequestItems sri ON SSR.SatelliteStoreRequestID = sri.SatelliteStoreRequestID
			JOIN Items I	ON	sri.ItemID = I.ItemID
			JOIN ItemCategories IC ON I.ItemCategoryID = IC.ItemCategoryID
			INNER JOIN Statuses S ON S.StatusID = SSR.StatusID
		WHERE
			(S.StatusName = 'Pending' /*Request*/
			OR S.StatusName = 'Partially Approved'
			OR S.StatusName = 'Ready for Pickup')
			AND IC.StoreID = @StoreID
			
	)
	BEGIN --if there aren't any statuses
		SET @results = 'No Pending Requests';
		
		SELECT
			@results;
	END
	ELSE
	BEGIN
		
		SET @results = NULL;
		
		SELECT
			@results;
			
		SELECT
			DISTINCT SSR.SatelliteStoreRequestID,
			SSR.RequestDescription,
			SSR.RequestDate,
			SSR.StoreID,
			st.StoreName -- Added to replace StoreID
		FROM
			SatelliteStoreRequests SSR
			LEFT JOIN StoreRequestItems sri ON SSR.SatelliteStoreRequestID = sri.SatelliteStoreRequestID
			LEFT JOIN Items I	ON	sri.ItemID = I.ItemID
			INNER JOIN Statuses S ON S.StatusID = SSR.StatusID
			INNER JOIN ItemCategories IC ON I.ItemCategoryID = IC.ItemCategoryID
			LEFT JOIN Stores st ON IC.StoreID = st.StoreID
		WHERE
			(S.StatusName = 'Pending' /*Request*/
			OR S.StatusName = 'Partially Approved' -- Needed to show Partially Approved Status Requests
			OR S.StatusName = 'Ready for Pickup')
			AND IC.StoreID = @StoreID

	END
END

GO

--IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getLPO')

--DROP PROCEDURE sp_getLPO

--GO

----Created by Michael Meyer Aug 2
--CREATE PROCEDURE [dbo].[sp_getLPO] (
--	@requisitionID INT
--)
--AS
--BEGIN
--DECLARE @results NVARCHAR(100)
--	IF NOT EXISTS (
--		SELECT
--			R.requisitionID
--		FROM
--			Requisitions R
--		WHERE
--			R.requisitionID = @requisitionID
--	)
--	BEGIN
--		SET @results = 'Not a valid requisition.'
--	END
	
	
--	SELECT
--		@results
		
--	SELECT
--		R.LPO
--	FROM
--		Requisitions R
--	WHERE
--		R.requisitionID = @requisitionID
--END

--GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getItemsByStoreUnitID')

DROP PROCEDURE sp_getItemsByStoreUnitID

GO

--created by michael meyer july 26, 2012
CREATE PROCEDURE [dbo].[sp_getItemsByStoreUnitID] (
	@storeID INT,
	@subunitID INT
)
AS
BEGIN
DECLARE @results NVARCHAR(100)

	IF EXISTS(
		SELECT
			I.itemID
		FROM
			ITEMS I
			INNER JOIN StoreUnitItems SUI ON SUI.itemID = I.itemID
			JOIN StoreUnits SU ON SU.StoreUnitID = SUI.StoreUnitID
		WHERE
			SU.StoreID = @storeID
			AND SU.SubUnitID = @subunitID
			
	)
	BEGIN
		SET @results = NULL
	END
	ELSE
	BEGIN
		SET @results = 'No items for the store unit.'
	END
	
	SELECT
		@results [errorMessage]
		
	SELECT
		I.itemID,
		I.itemName,
		SUI.StoreUnitQty
	FROM
		ITEMS I
		INNER JOIN StoreUnitItems SUI ON SUI.itemID = I.itemID
		JOIN StoreUnits SU ON SU.StoreUnitID = SUI.StoreUnitID
		WHERE
			SU.StoreID = @storeID
			AND SU.SubUnitID = @subunitID
			AND I.StatusID = 10
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getItems')

DROP PROCEDURE sp_getItems

GO

--Created by Preston Berrett June 26, 2012
CREATE PROCEDURE [dbo].[sp_getItems]
AS
BEGIN
DECLARE @results NVARCHAR(100)
	IF NOT EXISTS (
		SELECT
			I.ItemID,
			I.ItemName
		FROM
			Items I
	)
	BEGIN --if there aren't any pending requisitions
		SET @results = 'No Items';
		
		SELECT
			@results;
	END
	ELSE
	BEGIN
		
		SET @results = NULL;
		
		SELECT
			@results;
			
		SELECT
			I.ItemID,
			I.ItemName
		FROM
			Items I
		ORDER BY
			I.ItemName
	END
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getItemCategories')

DROP PROCEDURE sp_getItemCategories

GO

CREATE PROCEDURE [dbo].[sp_getItemCategories]

AS

BEGIN
	--error message table there 
	SELECT
		NULL [errorMessage]

	SELECT
		IC.ItemCategoryID,
		IC.ItemCategoryName
	FROM
		ItemCategories IC

END		

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getVendors')

DROP PROCEDURE sp_getVendors

GO

CREATE PROCEDURE [dbo].[sp_getVendors]

AS

BEGIN
	--error message table there 
	SELECT
		NULL [errorMessage]

	SELECT
		V.VendorName,
		V.VendorID
	FROM
		Vendors V

END		

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getItemByNameByStore')

DROP PROCEDURE sp_getItemByNameByStore

GO

CREATE PROCEDURE [dbo].[sp_getItemByNameByStore] (
	@itemName NVARCHAR(100),
	@StoreID	int
)
AS

BEGIN
DECLARE @results NVARCHAR(100)
	IF NOT EXISTS(
		SELECT
			I.itemID
		FROM
			Items I
			JOIN ItemCategories IC ON I.ItemCategoryID = IC.ItemCategoryID
		WHERE
			I.itemName LIKE ('%' + @itemName + '%')
			AND IC.StoreID = @StoreID
	)
	BEGIN --if the item doesn't exist
		SET @results = 'There is no item by that name.'
		SELECT
			@results
		
		SELECT
			NULL
	END
	ELSE --if the item exists
	BEGIN 
		SET @results = NULL
		
		SELECT
			@results
			
		SELECT
			I.itemID,
			--I.unitID,
			I.itemName,
			I.statusID,
			(SELECT TOP 1 VW.MarkupCost FROM vw_getCosts VW WHERE VW.ItemID = I.ItemID GROUP BY VW.ItemID, VW.MarkupCost ORDER BY VW.MarkupCost DESC) AS [markupCost],
			(SELECT ((SELECT sum(Inv.TransactionQty) FROM Inventory Inv WHERE Inv.ItemID = I.ItemID) - sum(H.HoldQty)) FROM Holds H WHERE H.ItemID = I.ItemID) AS [TotalQuantity],
			I.itemCategoryID
		FROM
			Items I
			JOIN ItemCategories IC ON I.ItemCategoryID = IC.ItemCategoryID
		WHERE
			I.itemName LIKE ('%' + @itemName + '%')
			AND IC.StoreID = @StoreID
			AND I.StatusID = 10
	END
	
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getItemByName')

DROP PROCEDURE sp_getItemByName

GO

CREATE PROCEDURE [dbo].[sp_getItemByName] (
	@itemName NVARCHAR(100)
)
AS

BEGIN
DECLARE @results NVARCHAR(100)
	IF NOT EXISTS(
		SELECT
			I.itemID
		FROM
			Items I
		WHERE
			I.itemName LIKE ('%' + @itemName + '%')
	)
	BEGIN --if the item doesn't exist
		SET @results = 'There is no item by that name.'
		SELECT
			@results
		
		SELECT
			NULL
	END
	ELSE --if the item exists
	BEGIN 
		SET @results = NULL
		
		SELECT
			@results
			
		SELECT
			I.itemID,
			--I.unitID,
			I.itemName,
			I.statusID,
			(SELECT TOP 1 VW.MarkupCost FROM vw_getCosts VW WHERE VW.ItemID = I.ItemID GROUP BY VW.ItemID, VW.MarkupCost ORDER BY VW.MarkupCost DESC) AS [markupCost],
			(SELECT ((SELECT sum(Inv.TransactionQty) FROM Inventory Inv WHERE Inv.ItemID = I.ItemID) - sum(H.HoldQty)) FROM Holds H WHERE H.ItemID = I.ItemID) AS [TotalQuantity],
			I.itemCategoryID,
			IC.StoreID,
			S.StoreName,
			IC.ItemCategoryName
		FROM
			Items I
			JOIN ItemCategories IC ON I.ItemCategoryID = IC.ItemCategoryID
			JOIN Stores S ON IC.StoreID = S.StoreID
		WHERE
			I.itemName LIKE ('%' + @itemName + '%')
			AND I.StatusID = 10
	END
	
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getItemByCategoryByStore')

DROP PROCEDURE sp_getItemByCategoryByStore

GO

CREATE PROCEDURE [dbo].[sp_getItemByCategoryByStore] (
	@itemCategoryID int,
	@StoreID	int
)
AS

BEGIN
DECLARE @results NVARCHAR(100)
	IF NOT EXISTS(
		SELECT
			I.itemID
		FROM
			Items I
			JOIN ItemCategories IC ON I.ItemCategoryID = IC.ItemCategoryID
		WHERE
			I.itemCategoryID = @itemCategoryID
			AND IC.StoreID = @StoreID
	)
	BEGIN --if the item doesn't exist
		SET @results = 'There is no item by that name.'
		SELECT
			@results
		
	END
	ELSE --if the item exists
	BEGIN 
		SET @results = NULL
		
		SELECT
			@results
			
		SELECT
			I.itemID,
			--I.unitID,
			I.itemName,
			I.statusID,
			(SELECT TOP 1 VW.MarkupCost FROM vw_getCosts VW WHERE VW.ItemID = I.ItemID GROUP BY VW.ItemID, VW.MarkupCost ORDER BY VW.MarkupCost DESC) AS [markupCost],
			--(SELECT sum(Inv.TransactionQty) FROM Inventory Inv WHERE I.ItemID = Inv.ItemID) AS [TotalQuantity],
			(SELECT ((SELECT sum(Inv.TransactionQty) FROM Inventory Inv WHERE Inv.ItemID = I.ItemID) - sum(H.HoldQty)) FROM Holds H WHERE H.ItemID = I.ItemID) AS [TotalQuantity],
			I.itemCategoryID
		FROM
			Items I
			JOIN ItemCategories IC ON I.ItemCategoryID = IC.ItemCategoryID
		WHERE
			I.itemCategoryID = @itemCategoryID
			AND IC.StoreID = @StoreID
	END
	
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getItemByCategory')

DROP PROCEDURE sp_getItemByCategory

GO

CREATE PROCEDURE [dbo].[sp_getItemByCategory] (
	@itemCategoryID int
)
AS

BEGIN
DECLARE @results NVARCHAR(100)
	IF NOT EXISTS(
		SELECT
			I.itemID
		FROM
			Items I
		WHERE
			I.itemCategoryID = @itemCategoryID
	)
	BEGIN --if the item doesn't exist
		SET @results = 'There is no item by that name.'
		SELECT
			@results
		
	END
	ELSE --if the item exists
	BEGIN 
		SET @results = NULL
		
		SELECT
			@results
			
		SELECT
			I.itemID,
			--I.unitID,
			I.itemName,
			I.statusID,
			(SELECT TOP 1 VW.MarkupCost FROM vw_getCosts VW WHERE VW.ItemID = I.ItemID GROUP BY VW.ItemID, VW.MarkupCost ORDER BY VW.MarkupCost DESC) AS [markupCost],
			(SELECT ((SELECT sum(Inv.TransactionQty) FROM Inventory Inv WHERE Inv.ItemID = I.ItemID) - sum(H.HoldQty)) FROM Holds H WHERE H.ItemID = I.ItemID) AS [TotalQuantity],
			I.itemCategoryID,
			IC.storeID,
			S.StoreName,
			IC.ItemCategoryName
		FROM
			Items I
			JOIN ItemCategories IC ON I.ItemCategoryID = IC.ItemCategoryID
			JOIN Stores S ON IC.StoreID = S.StoreID
		WHERE
			I.itemCategoryID = @itemCategoryID
			AND I.StatusID = 10
	END
	
END

GO

--created by Travis Marceau
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getModifyItemInfo')

DROP PROCEDURE sp_getModifyItemInfo

GO

CREATE PROCEDURE [dbo].[sp_getModifyItemInfo] (
	@itemID int
)
AS

BEGIN
DECLARE @results NVARCHAR(100)
	IF NOT EXISTS(
		SELECT
			*
		FROM
			Items I
		WHERE
			I.itemID = @itemID
	)
	BEGIN --if the item doesn't exist
		SET @results = 'There is no item with that ID.'
		SELECT
			@results
		
	END
	ELSE --if the item exists
	BEGIN 
		SET @results = NULL
		
		SELECT
			@results
			
		SELECT
			I.itemID,
			I.unitID,
			I.itemName,
			I.statusID,
			(SELECT TOP 1 VW.MarkupCost FROM vw_getCosts VW WHERE VW.ItemID = I.ItemID GROUP BY VW.ItemID, VW.MarkupCost ORDER BY VW.MarkupCost DESC) AS [markupCost],
			(SELECT ((SELECT sum(Inv.TransactionQty) FROM Inventory Inv WHERE Inv.ItemID = I.ItemID) - sum(H.HoldQty)) FROM Holds H WHERE H.ItemID = I.ItemID) AS [totalQuantity],
			I.itemCategoryID,
			IC.storeID,
			S.StoreName,
			IC.ItemCategoryName,
			VC.VendorCost,
			V.VendorID,
			V.VendorName,
			VC.MarkupMultiplier,
			I.EmergencyQty,
			I.minStockQty
		FROM
			Items I
			JOIN ItemCategories IC ON I.ItemCategoryID = IC.ItemCategoryID
			JOIN Stores S ON IC.StoreID = S.StoreID
			JOIN VendorCosts VC ON I.ItemID = VC.ItemID
			JOIN Vendors V ON V.VendorID = VC.VendorID
		WHERE
			I.ItemID = @itemID
	END
	
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getItemAdjustmentReasons')

DROP PROCEDURE sp_getItemAdjustmentReasons

GO

--Created by Michael Meyer
CREATE PROCEDURE [dbo].[sp_getItemAdjustmentReasons] 
AS
BEGIN
DECLARE @results NVARCHAR(100)
	IF EXISTS (
		SELECT TOP 1
			AJ.AdjReasonID
		FROM
			AdjReasons AJ
	)
	BEGIN
		SET @results = NULL
	END
	ELSE
	BEGIN
		SET @results = 'No item adjustments'
	END
	
	SELECT
		@results [errorMessage]
		
	SELECT
		AJ.AdjReasonID,
		AJ.AdjName
	FROM
		AdjReasons AJ
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getHint')

DROP PROCEDURE sp_getHint

GO

--Created by Michael Meyer 5/24/2012
CREATE PROCEDURE [dbo].[sp_getHint] (
	@username NVARCHAR(255)
)
AS
BEGIN
	DECLARE @results NVARCHAR(255)

	SET @results = NULL
	--checks to see if the user exists
	IF NOT EXISTS (
		SELECT
			U.username
		FROM
			Users U
		WHERE
			U.username = @username
	)
		BEGIN
			SET @results = 'Not a valid username.'
			
			SELECT
				@results
				
			SELECT
				NULL
		END
	ELSE
		BEGIN
		--checks to see if the user has a hint
		IF NOT EXISTS (
			SELECT
				U.PasswordHint
			FROM
				Users U
			WHERE
				U.username = @username
		)
			--if user doen't have a hint returns an error message
			BEGIN
				SET @results = 'No hint available, see system admin. User or hint doesn''t exist'
				--returns the error message in one table
				SELECT
					@results
				--returns an empty table 
				SELECT
					NULL
			END
			
		ELSE --if there is a user
			BEGIN
				SET @results = NULL
				--returns the error table
				SELECT
					@results
				--returns the password hint for the username
				SELECT
					U.PasswordHint
				FROM
					Users U
				WHERE
					U.username = @username	
			END
	END
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getCategoryByStore')

DROP PROCEDURE sp_getCategoryByStore

GO

--Created by Benjamin Strickler 08/15/2012
CREATE PROCEDURE [dbo].[sp_getCategoryByStore]
	@StoreID	int

AS
BEGIN
	--there will never be any errors
	SELECT
		NULL
		
	SELECT DISTINCT
		IC.ItemCategoryID,
		IC.ItemCategoryName
	FROM
		ItemCategories IC
	JOIN
		Items I ON IC.ItemCategoryID = I.ItemCategoryID
	WHERE
		IC.StoreID = @StoreID
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getCategory')

DROP PROCEDURE sp_getCategory

GO

--Created by Michael Meyer June 6, 2012
CREATE PROCEDURE [dbo].[sp_getCategory]
AS
BEGIN
	--there will never be any errors
	SELECT
		NULL
		
	SELECT
		ItemCategoryID,
		ItemCategoryName,
		StoreID
	FROM
		ItemCategories
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getAvailableVendorStatuses')

DROP PROCEDURE sp_getAvailableVendorStatuses

GO

--Created by Michael Meyer June 12, 2012
CREATE PROCEDURE [dbo].[sp_getAvailableVendorStatuses]
AS
BEGIN
DECLARE @results NVARCHAR(100)
	IF NOT EXISTS (
		SELECT
			S.statusID,
			S.StatusName
		FROM
			Statuses S
			INNER JOIN StatusTypes ST ON ST.StatusTypeID = S.StatusTypeID
		WHERE
			ST.StatusTypeID = 3 /*vendor*/
	)
	BEGIN --if there aren't any statuses
		SET @results = 'No available user statuses'
	END
	ELSE
	BEGIN
		
		SET @results = NULL
		
		SELECT
			@results
			
		SELECT
			S.statusID,
			S.StatusName
		FROM
			Statuses S
			INNER JOIN StatusTypes ST ON ST.StatusTypeID = S.StatusTypeID
		WHERE
			ST.StatusTypeID = 3 /*vendor*/
	END
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getAvailableUserStatuses')

DROP PROCEDURE sp_getAvailableUserStatuses

GO

CREATE PROCEDURE [dbo].[sp_getAvailableUserStatuses]
AS
BEGIN
DECLARE @results NVARCHAR(100)
	IF NOT EXISTS (
		SELECT
			S.statusID,
			S.StatusName
		FROM
			Statuses S
			INNER JOIN StatusTypes ST ON ST.StatusTypeID = S.StatusTypeID
		WHERE
			ST.StatusTypeID = 2 /*Users*/
	)
	BEGIN --if there aren't any statuses
		SET @results = 'No available user statuses'
	END
	ELSE
	BEGIN
		
		SET @results = NULL
		
		SELECT
			@results
			
		SELECT
			S.statusID,
			S.StatusName
		FROM
			Statuses S
			INNER JOIN StatusTypes ST ON ST.StatusTypeID = S.StatusTypeID
		WHERE
			ST.StatusTypeID = 2 /*Users*/
	END
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getAvailableSubUnitStatuses')

DROP PROCEDURE sp_getAvailableSubUnitStatuses

GO

--Created by Michael Meyer June 12, 2012
CREATE PROCEDURE [dbo].[sp_getAvailableSubUnitStatuses]
AS
BEGIN
DECLARE @results NVARCHAR(100)
	IF NOT EXISTS (
		SELECT
			S.statusID,
			S.StatusName
		FROM
			Statuses S
			INNER JOIN StatusTypes ST ON ST.StatusTypeID = S.StatusTypeID
		WHERE
			ST.StatusTypeID = 7 /*SubUnit*/
	)
	BEGIN --if there aren't any statuses
		SET @results = 'No available user statuses'
	END
	ELSE
	BEGIN
		
		SET @results = NULL
		
		SELECT
			@results
			
		SELECT
			S.statusID,
			S.StatusName
		FROM
			Statuses S
			INNER JOIN StatusTypes ST ON ST.StatusTypeID = S.StatusTypeID
		WHERE
			ST.StatusTypeID = 7 /*SubUnit*/
	END
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getAvailableStoreUnitStatuses')

DROP PROCEDURE sp_getAvailableStoreUnitStatuses

GO

--Created by Michael Meyer Jul 5 2012
CREATE PROCEDURE [dbo].[sp_getAvailableStoreUnitStatuses]
AS
BEGIN
DECLARE @results NVARCHAR(100)
	IF NOT EXISTS (
		SELECT
			S.statusID,
			S.StatusName
		FROM
			Statuses S
			INNER JOIN StatusTypes ST ON ST.StatusTypeID = S.StatusTypeID
		WHERE
			ST.StatusTypeID = 11 /*Store Unit*/
	)
	BEGIN --if there aren't any statuses
		SET @results = 'No available user statuses'
	END
	ELSE
	BEGIN
		
		SET @results = NULL
		
		SELECT
			@results
			
		SELECT
			S.statusID,
			S.StatusName
		FROM
			Statuses S
			INNER JOIN StatusTypes ST ON ST.StatusTypeID = S.StatusTypeID
		WHERE
			ST.StatusTypeID = 11 /*StoreUnits*/
	END
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getAvailableStoreTypeStatuses')

DROP PROCEDURE sp_getAvailableStoreTypeStatuses

GO

--Created by Michael Meyer June 12, 2012
CREATE PROCEDURE [dbo].[sp_getAvailableStoreTypeStatuses]
AS
BEGIN
DECLARE @results NVARCHAR(100)
	IF NOT EXISTS (
		SELECT
			S.statusID,
			S.StatusName
		FROM
			Statuses S
			INNER JOIN StatusTypes ST ON ST.StatusTypeID = S.StatusTypeID
		WHERE
			ST.StatusTypeID = 6 /*StoreType*/
	)
	BEGIN --if there aren't any statuses
		SET @results = 'No available user statuses'
	END
	ELSE
	BEGIN
		
		SET @results = NULL
		
		SELECT
			@results
			
		SELECT
			S.statusID,
			S.StatusName
		FROM
			Statuses S
			INNER JOIN StatusTypes ST ON ST.StatusTypeID = S.StatusTypeID
		WHERE
			ST.StatusTypeID = 6 /*StoreType*/
	END
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getAvailableStoreStatuses')

DROP PROCEDURE sp_getAvailableStoreStatuses

GO

--Created by Michael Meyer June 12, 2012
CREATE PROCEDURE [dbo].[sp_getAvailableStoreStatuses]
AS
BEGIN
DECLARE @results NVARCHAR(100)
	IF NOT EXISTS (
		SELECT
			S.statusID,
			S.StatusName
		FROM
			Statuses S
			INNER JOIN StatusTypes ST ON ST.StatusTypeID = S.StatusTypeID
		WHERE
			ST.StatusTypeID = 5 /*Store*/
	)
	BEGIN --if there aren't any statuses
		SET @results = 'No available user statuses'
	END
	ELSE
	BEGIN
		
		SET @results = NULL
		
		SELECT
			@results
			
		SELECT
			S.statusID,
			S.StatusName
		FROM
			Statuses S
			INNER JOIN StatusTypes ST ON ST.StatusTypeID = S.StatusTypeID
		WHERE
			ST.StatusTypeID = 5 /*Store*/
	END
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getAvailableSatelliteStoreRequestStatuses')

DROP PROCEDURE sp_getAvailableSatelliteStoreRequestStatuses

GO

--Created by Michael Meyer June 12, 2012
CREATE PROCEDURE [dbo].[sp_getAvailableSatelliteStoreRequestStatuses]
AS
BEGIN
DECLARE @results NVARCHAR(100)
	IF NOT EXISTS (
		SELECT
			S.statusID,
			S.StatusName
		FROM
			Statuses S
			INNER JOIN StatusTypes ST ON ST.StatusTypeID = S.StatusTypeID
		WHERE
			ST.StatusTypeID = 8 /*SatelliteStoreRequest*/
	)
	BEGIN --if there aren't any statuses
		SET @results = 'No available user statuses'
	END
	ELSE
	BEGIN
		
		SET @results = NULL
		
		SELECT
			@results
			
		SELECT
			S.statusID,
			S.StatusName
		FROM
			Statuses S
			INNER JOIN StatusTypes ST ON ST.StatusTypeID = S.StatusTypeID
		WHERE
			ST.StatusTypeID = 8 /*SatelliteStoreRequest*/
	END
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getAvailableRequisitionStatuses')

DROP PROCEDURE sp_getAvailableRequisitionStatuses

GO

--Created by Michael Meyer June 12, 2012
CREATE PROCEDURE [dbo].[sp_getAvailableRequisitionStatuses]
AS
BEGIN
DECLARE @results NVARCHAR(100)
	IF NOT EXISTS (
		SELECT
			S.statusID,
			S.StatusName
		FROM
			Statuses S
			INNER JOIN StatusTypes ST ON ST.StatusTypeID = S.StatusTypeID
		WHERE
			ST.StatusTypeID = 4 /*Requisition*/
	)
	BEGIN --if there aren't any statuses
		SET @results = 'No available user statuses'
	END
	ELSE
	BEGIN
		
		SET @results = NULL
		
		SELECT
			@results
			
		SELECT
			S.statusID,
			S.StatusName
		FROM
			Statuses S
			INNER JOIN StatusTypes ST ON ST.StatusTypeID = S.StatusTypeID
		WHERE
			ST.StatusTypeID = 4 /*Requisition*/
	END
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getAvailableItemStatuses')

DROP PROCEDURE sp_getAvailableItemStatuses

GO

--Created by Michael Meyer June 12, 2012
CREATE PROCEDURE [dbo].[sp_getAvailableItemStatuses]
AS
BEGIN
DECLARE @results NVARCHAR(100)
	IF NOT EXISTS (
		SELECT
			S.statusID,
			S.StatusName
		FROM
			Statuses S
			INNER JOIN StatusTypes ST ON ST.StatusTypeID = S.StatusTypeID
		WHERE
			ST.StatusTypeID = 1 /*Item*/
	)
	BEGIN --if there aren't any statuses
		SET @results = 'No available user statuses'
	END
	ELSE
	BEGIN
		
		SET @results = NULL
		
		SELECT
			@results
			
		SELECT
			S.statusID,
			S.StatusName
		FROM
			Statuses S
			INNER JOIN StatusTypes ST ON ST.StatusTypeID = S.StatusTypeID
		WHERE
			ST.StatusTypeID = 1 /*Item*/
	END
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getAllUsers')

DROP PROCEDURE sp_getAllUsers

GO

--Created by Michael Meyer May 28 2012
CREATE PROCEDURE [dbo].[sp_getAllUsers]
AS
BEGIN
	--this is for the error table.  Never will have any errors
	SELECT
		NULL [errors]
		
	--selects all users and their status
	SELECT
		U.userID,
		U.firstName + ' ' + U.LastName [name],
		U.UserName,
		U.pass,
		U.passwordHint,
		S.statusName,
		U.storeID
	FROM
		Users U
		INNER JOIN Statuses S on S.statusID = U.statusID
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getAllStores')

DROP PROCEDURE sp_getAllStores

GO

--Created by Michael Meyer June 18 2012
CREATE PROCEDURE [dbo].[sp_getAllStores]
AS
BEGIN
	--this is for the error table.  Never will have any errors
	SELECT
		NULL [errors]
		
	--selects all users and their status
	SELECT
		ST.storeID,
		ST.storeName,
		S.StatusName
	FROM
		Stores ST
		INNER JOIN Statuses S on S.statusID = ST.statusID
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getAllItemsByStoreID')

DROP PROCEDURE sp_getAllItemsByStoreID

GO

--created by michael meyer july 26, 2012
--Modifed by Adam Venezia 10-3-2012
CREATE PROCEDURE [dbo].[sp_getAllItemsByStoreID] (
    @storeID INT,
    @subunitID INT
)
AS
BEGIN
DECLARE @results NVARCHAR(100)

    IF EXISTS(
        SELECT
            I.itemID
        FROM
            ITEMS I
			JOIN ItemCategories IC ON I.ItemCategoryID = IC.ItemCategoryID
        WHERE
            IC.StoreID = @StoreID
            
    )
    BEGIN
        SET @results = NULL
    END
    ELSE
    BEGIN
        SET @results = 'No items for the store.'
    END
    
    SELECT
        @results [errorMessage]
        
    SELECT
        I.itemID,
        I.itemName,
        (SELECT ((SELECT sum(Inv.TransactionQty) FROM Inventory Inv WHERE Inv.ItemID = I.ItemID) - sum(H.HoldQty)) FROM Holds H WHERE H.ItemID = I.ItemID) AS [TotalQuantity],
        --SUI.StoreUnitQty--,
        (SELECT TOP 1 VW.MarkupCost FROM vw_getCosts VW WHERE VW.ItemID=I.ItemID GROUP BY VW.ItemID, VW.MarkupCost ORDER BY VW.MarkupCost DESC) AS [MarkupCost]
    FROM
        ITEMS I
		JOIN ItemCategories IC ON I.ItemCategoryID = IC.ItemCategoryID
        --INNER JOIN StoreUnitItems SUI ON SUI.itemID = I.itemID
        --JOIN StoreUnits SU ON SU.StoreUnitID = SUI.StoreUnitID
        WHERE
            IC.StoreID = @storeID
            --AND SU.SubUnitID = @subunitID
END

GO


IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getActiveVendors')

DROP PROCEDURE sp_getActiveVendors

GO

--Created by Michael Meyer July 9, 2012
CREATE PROCEDURE [dbo].[sp_getActiveVendors]
AS
BEGIN
	DECLARE @results NVARCHAR(100)
	
	DECLARE @statusID INT
	SET @statusID = (
		SELECT TOP 1
			S.statusID
		FROM
			Statuses S
			INNER JOIN StatusTypes ST ON ST.statusTypeID = S.statusTypeID
				AND ST.statusTypeName = 'Vendor'
				AND S.statusName = 'Active'
	)
	
	IF EXISTS(
		SELECT TOP 1
			V.vendorID
		FROM
			Vendors V
			INNER JOIN Statuses S ON S.statusID = V.statusID
		WHERE
			S.statusName = 'Active'
	)
	BEGIN
		SET @results = NULL
	END
	ELSE
		SET @results = 'No active vendors'
	BEGIN
		SELECT
			@results [errorMessage]

		SELECT
			V.vendorID,
			V.vendorName
		FROM
			Vendors V
		WHERE
			V.statusID = @statusID
	END
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getActiveSubUnits')

DROP PROCEDURE sp_getActiveSubUnits

GO

--Created by Michael Meyer July 5 2012
CREATE PROCEDURE [dbo].[sp_getActiveSubUnits]
AS
BEGIN
	DECLARE @results NVARCHAR(100)

	IF NOT EXISTS(
		SELECT TOP 1
			S.subunitID
		FROM
			Subunits S
			INNER JOIN Statuses STAT ON STAT.statusID = S.statusID
		WHERE
			STAT.StatusName = 'Active'
	)
	BEGIN
		SET @results = 'There are no active subunits.'
		
		SELECT
			@results [ErrorMessage]
			
		SELECT
			NULL
	END
	ELSE
	BEGIN
		SET @results = NULL
		
		SELECT
			@results [ErrorMessage]
		
		SELECT
			S.subunitID,
			S.subunitName,
			S.statusID,
			STAT.StatusName
		FROM
			Subunits S
			INNER JOIN Statuses STAT ON STAT.statusID = S.statusID
		WHERE
			STAT.StatusName = 'Active'
	END
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getActiveCentralStores')

DROP PROCEDURE sp_getActiveCentralStores

GO

--Created by Michael Meyer July 5 2012
CREATE PROCEDURE [dbo].[sp_getActiveCentralStores]
AS
BEGIN
	DECLARE @results NVARCHAR(100)

	IF NOT EXISTS(
		SELECT TOP 1
			S.storeID
		FROM
			Stores S
			INNER JOIN Statuses STAT ON STAT.statusID = S.statusID
		WHERE
			S.storeTypeID = 1	/*	Central Store	*/
			AND STAT.StatusName = 'Active'
	)
	BEGIN
		SET @results = 'There are no active central stores.'
		
		SELECT
			@results [ErrorMessage]
			
		SELECT
			NULL
	END
	ELSE
	BEGIN
		SET @results = NULL
		
		SELECT
			@results [ErrorMessage]
		
		SELECT
			S.storeID,
			S.storeName,
			S.statusID,
			STAT.StatusName
		FROM
			Stores S
			INNER JOIN Statuses STAT ON STAT.statusID = S.statusID
		WHERE
			S.storeTypeID = 1	/*	Central Store	*/
			AND STAT.StatusName = 'Active'
	END
END

GO


IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_FillRequestItem')

DROP PROCEDURE sp_FillRequestItem

GO

CREATE PROCEDURE [dbo].[sp_FillRequestItem] (
	@RequestID		int,
	@ItemID			int,
	@QtyReceived	int,
	@StoreID		int	
)
AS

BEGIN
DECLARE @results NVARCHAR(100)
DECLARE @MinusTotal		int --The Amount to minus from the total Quantity
DECLARE @MinusEmergency int --The Amount to minus from the Emergency
DECLARE	@Emergency		int --Set the Amount to be able to use sp_updateEmergencyQty

DECLARE @TotalQuantity	int
SET @TotalQuantity = (SELECT ((SELECT sum(Inv.TransactionQty) FROM Inventory Inv WHERE Inv.ItemID = @ItemID) - sum(H.HoldQty)) FROM Holds H WHERE H.ItemID = @ItemID)

IF((@TotalQuantity - @QtyReceived) < 0)
BEGIN
	SET @results = 'Not enough stock.'
END
ELSE
BEGIN
	SET @MinusEmergency = 0
	SET	@MinusTotal = @QtyReceived

DECLARE @RequestItem	int --Gets the ItemID from the request to insert into the receiving history
SET @RequestItem = (SELECT sri.StoreRequestItemID 
					FROM StoreRequestItems sri 
					WHERE sri.ItemID = @ItemID AND  
					sri.SatelliteStoreRequestID = @RequestID)

DECLARE @SubUnitID	int --Gets the subunitID based off of the store name to locate the StoreUnit
SET @SubUnitID = (SELECT sub.SubUnitID
					FROM SubUnits sub
					JOIN Stores s on sub.SubUnitName = s.StoreName
					JOIN SatelliteStoreRequests ssr on ssr.StoreID = s.StoreID
					WHERE
						ssr.SatelliteStoreRequestID = @RequestID)
					
DECLARE @StoreUnitID	int
SET @StoreUnitID = (SELECT su.StoreUnitID
						FROM StoreUnits su 
						WHERE su.StoreID = @StoreID
						AND su.SubUnitID = @SubUnitID)
						
DECLARE @StoreUnitItemID	int
SET @StoreUnitItemID = (SELECT StoreUnitItemID
							FROM StoreUnitItems
							WHERE StoreUnitID = @StoreUnitID
							AND ItemID = @ItemID)
					
DECLARE @EmergencyQty	int
SET @EmergencyQty = (SELECT StoreUnitQty
							FROM StoreUnitItems
							WHERE StoreUnitItemID = @StoreUnitItemID)
	BEGIN 
		--UPDATE Items
		--SET TotalQuantity = TotalQuantity - @MinusTotal
		--WHERE ItemID = @ItemID
		
		IF(@MinusEmergency != 0)
		BEGIN
			SET @Emergency = @EmergencyQty - @MinusEmergency
			EXEC sp_updateEmergencyQty @ItemID, @Emergency
		END

		
		INSERT INTO RequestReceivedItems (ReceivedDate, QtyReceived, StoreRequestItemID)
		VALUES (GETDATE(), @QtyReceived, @RequestItem)
	END
	
END
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_deleteRequisitionItem')

DROP PROCEDURE sp_deleteRequisitionItem

GO

--Created by Preston Berrett July 19, 2012
--Modified by Adam Venezia 10-15-12
--Removed the StoreID aspect of this stored procedure. The UI no longer had that ID on hand
--and was crashing trying to convert "medical" to 1 for example. Still works as expected.
CREATE PROCEDURE [dbo].[sp_deleteRequisitionItem]
	@RequisitionID INT,
	@ItemID INT,
	@StoreID INT
AS
BEGIN
DECLARE @results NVARCHAR(100)
	IF NOT EXISTS (
		SELECT
			RI.RequisitionItemID
		FROM
			RequisitionItems RI
			JOIN Requisitions R ON R.RequisitionID = RI.RequisitionID
		WHERE
			RI.RequisitionID = @RequisitionID
			AND
			RI.ItemID = @ItemID
			AND
			R.StoreID = @StoreID	
	)
	BEGIN -- if there is no requisition with that item
		SET @results = 'No Requisitions';
		
		SELECT
			@results;
	END
	ELSE
	BEGIN
		
		SET @results = NULL;
		
		SELECT
			@results;
			
		DELETE RequisitionItems
		WHERE 
			RequisitionID = @RequisitionID
			AND
			ItemID = @ItemID
			
		SELECT
			'Item has been removed from the Requisition.'
	END
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_DeleteRequestItem')

DROP PROCEDURE sp_DeleteRequestItem

GO

CREATE PROCEDURE [dbo].[sp_DeleteRequestItem] (
	@RequestID		int,
	@ItemID			int
)
AS

BEGIN
		
		DELETE FROM StoreRequestItems
		WHERE SatelliteStoreRequestID = @RequestID 
		AND ItemID = @ItemID

END

GO


IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_CreateRequest')

DROP PROCEDURE sp_CreateRequest

GO

CREATE PROCEDURE [dbo].[sp_CreateRequest]
	@StoreID				int,
	@RequestDescription		varchar(100)
AS
BEGIN
DECLARE @results NVARCHAR(100)
	IF EXISTS (
		SELECT
			R.SatelliteStoreRequestID
		FROM
			SatelliteStoreRequests R
		WHERE
			R.StoreID = @StoreID AND
			R.StatusID = 84
	)
	BEGIN --if there are any creating requests for this store
		SET @results = 'Request already exists'
		
		SELECT
			@results;
	END
	ELSE
	BEGIN
			
		INSERT SatelliteStoreRequests(StoreID, RequestDate, StatusID, RequestDescription)
			VALUES (@StoreID, GETDATE(), 84, @RequestDescription)
		
		DECLARE @ReqID int
		SELECT @ReqID = @@IDENTITY
		
		SET @results = NULL;
		
		SELECT
			@results; 
		
		SELECT
			r.SatelliteStoreRequestID, r.RequestDate, r.RequestDescription
		FROM
			SatelliteStoreRequests r
		WHERE
			r.SatelliteStoreRequestID = @ReqID
		
	END
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_completeRequisition')

DROP PROCEDURE sp_completeRequisition

GO

--Created by Preston Berrett July 26, 2012
CREATE PROCEDURE [dbo].[sp_completeRequisition]
	@RequisitionID INT
AS
BEGIN
DECLARE @results NVARCHAR(100)
	IF NOT EXISTS (
		SELECT
			R.RequisitionID
		FROM
			RequisitionItems R
		WHERE
			R.RequisitionID = @RequisitionID	
	)
	BEGIN --if there aren't any pending requisitions
		SET @results = 'False';
		
		SELECT
			@results;
	END
	ELSE
	BEGIN
			
		UPDATE Requisitions
		SET StatusID = 41
		WHERE RequisitionID = @RequisitionID
		
		SELECT
			'True';
	END
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_checkToCloseRequisition')

DROP PROCEDURE sp_checkToCloseRequisition

GO

--Created by Benjamin Strickler Aug. 10, 2012
CREATE PROCEDURE [dbo].[sp_checkToCloseRequisition] (
	@RequisitionID INT
)
AS
BEGIN
DECLARE @results NVARCHAR(100)
	IF NOT EXISTS (
		SELECT
			R.RequisitionID,
			RI.ItemID,
			I.ItemName,
			S.StatusName,
			RI.RequisitionQty
		FROM
			Requisitions R
			INNER JOIN RequisitionItems RI ON RI.RequisitionID = R.RequisitionID
			INNER JOIN Items I ON I.ItemID = RI.ItemID
			LEFT JOIN Statuses S ON S.statusID = RI.statusID
		WHERE
			R.RequisitionID = @RequisitionID
			AND RI.StatusID != 41	
	)
	BEGIN --if there aren't any pending requisitions
		SET @results = 'true';
		
		SELECT
			@results;
	END
	ELSE
	BEGIN
		SET @results = 'false';
		
		SELECT
			@results;
	END
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_checkSignature')

DROP PROCEDURE sp_checkSignature

GO

CREATE PROCEDURE [dbo].[sp_checkSignature]
	@RequisitionID	int
AS
BEGIN
	DECLARE @results NVARCHAR(100)
	IF NOT EXISTS (
			SELECT 
				* 
			FROM 
				 RequisitionItems
			WHERE 
				RequisitionID = @RequisitionID)
	BEGIN
		SET @results = 'Requisition Does Not Exist';
			
		SELECT
		@results;
	END
	ELSE
	BEGIN
	
		SELECT
			@results;
		
		SELECT
			ISNULL(GM.FirstName, N'Waiting Approval') as GM,
			ISNULL(CEO.FirstName, N'Waiting Approval') as CEO
		FROM
			Requisitions R
			FULL JOIN Users GM ON GM.UserID = R.GMSig
			FULL JOIN Users CEO ON CEO.UserID = R.CEOSig
		WHERE
			RequisitionID = @RequisitionID
		
	END
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_CheckOpenRequisition')

DROP PROCEDURE sp_CheckOpenRequisition

GO

CREATE PROCEDURE [dbo].[sp_CheckOpenRequisition]
	@StoreID				int
AS
BEGIN
DECLARE @results NVARCHAR(100)
IF NOT EXISTS
	(SELECT * FROM Requisitions
		WHERE StoreID = @StoreID AND StatusID = 45)
	BEGIN 
		SET @results = 'No requests found';
		
		SELECT
			@results;
	END	
ELSE			
BEGIN
	SET @results = NULL;
		
	SELECT @results;
	
	SELECT
		RequisitionID,
		OrderDate,
		RequisitionName
	FROM
		Requisitions
	WHERE
		StoreID = @StoreID AND StatusID = 45;
END
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_checkOpenOutsideProcurement')

DROP PROCEDURE sp_checkOpenOutsideProcurement

GO

CREATE PROCEDURE [dbo].[sp_checkOpenOutsideProcurement]
	@StoreID	int
AS
BEGIN
DECLARE @results NVARCHAR(100)
IF NOT EXISTS
	(SELECT * FROM Requisitions
		WHERE StoreID = @StoreID AND StatusID = 46)
	BEGIN 
		SET @results = 'No requests found';
		
		SELECT
			@results;
	END	
ELSE			
BEGIN
	SET @results = NULL;
		
	SELECT @results;
	
	SELECT
		RequisitionID,
		OrderDate,
		RequisitionName
	FROM
		Requisitions
	WHERE
		StoreID = @StoreID AND StatusID = 46;
END
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_CheckOpenRequest')

DROP PROCEDURE sp_CheckOpenRequest

GO

CREATE PROCEDURE [dbo].[sp_CheckOpenRequest]
	@StoreID				int
AS
BEGIN
DECLARE @results NVARCHAR(100)
IF NOT EXISTS
	(SELECT * FROM SatelliteStoreRequests
		WHERE StoreID = @StoreID AND StatusID = 84)
	BEGIN 
		SET @results = 'No requests found';
		
		SELECT
			@results;
	END	
ELSE			
BEGIN
	SET @results = NULL;
		
	SELECT @results;
	
	SELECT
		SatelliteStoreRequestID,
		RequestDate,
		RequestDescription
	FROM
		SatelliteStoreRequests
	WHERE
		StoreID = @StoreID AND StatusID = 84;
END
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_changeRequisitionItemQty')

DROP PROCEDURE sp_changeRequisitionItemQty

GO

/* Written by Preston Berrett July 19, 2012 */
CREATE PROCEDURE [dbo].[sp_changeRequisitionItemQty]
	@RequisitionID	int,
	@ItemID			int,
	@Qty			int
AS
BEGIN
	DECLARE @results NVARCHAR(100)
	IF NOT EXISTS (
			SELECT 
				* 
			FROM 
				 RequisitionItems
			WHERE 
				RequisitionID = @RequisitionID 
				AND ItemID = @ItemID)
	BEGIN
		SET @results = 'Requisition does not exist';
			
		SELECT
		@results;
	END
	ELSE
	BEGIN
		
		UPDATE RequisitionItems
		SET RequisitionQty = @Qty
		WHERE
			RequisitionID = @RequisitionID
			AND
			ItemID = @ItemID
		
		SET @results = 'Item quantity has been updated';
			
		SELECT
		@results;
	END
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_allocatNewItem')

DROP PROCEDURE sp_allocatNewItem

GO

--created by michael meyer aug 11, 2012

CREATE PROCEDURE [dbo].[sp_allocatNewItem] (
	@itemName NVARCHAR(MAX),
	@storeID INT
)

AS
BEGIN

DECLARE @itemID INT

	--gets the inserted item id that was just inserted.
	SELECT
		@itemID = I.itemID
	FROM
		Items I
		JOIN ItemCategories IC ON I.ItemCategoryID = IC.ItemCategoryID
	WHERE
		I.itemName = @itemName
		AND IC.storeID = @storeID

	--makes the item available for the store units
	INSERT INTO storeUnitItems (ItemID, StoreUnitQty, storeUnitID)
		SELECT
			@itemID,
			0 [quantity],
			SU.storeUnitID
		FROM
			Items I
			INNER JOIN ItemCategories IC ON I.ItemCategoryID = IC.ItemCategoryID
			INNER JOIN StoreUnitItems SUI ON SUI.itemID = I.itemID
			INNER JOIN StoreUnits SU ON SU.storeID = IC.storeID
				AND SU.storeUnitID = SUI.storeUnitID
				AND SU.storeID = @storeID
				AND I.itemID = @itemID
END


GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_allocateItemDeprecated')

DROP PROCEDURE sp_allocateItemDeprecated

GO

--Created by Michael Meyer Aug 1 2012
CREATE PROCEDURE [dbo].[sp_allocateItemDeprecated] (
	@storeID INT,
	@subUnitID INT,
	@itemID INT,
	--@itemUnit INT,
	@itemQuantity INT
)

AS
BEGIN
	DECLARE @results NVARCHAR(100)
	DECLARE @completed BIT
		SET @completed = 1
	
	IF NOT EXISTS (
		SELECT
			S.storeID
		FROM
			stores S
		WHERE
			S.storeID = @storeID
	)
	BEGIN
		SET @results = 'Store does not exist.'
		SET @completed = 0
	END
	
	--IF NOT EXISTS (
	--	SELECT
	--		I.itemID
	--	FROM
	--		items I
	--	WHERE
	--		I.itemID = @itemID
	--)
	--BEGIN
	--	SET @results = 'Item does not exist.'
	--	SET @completed = 0
	--END
	
	--IF NOT EXISTS (
	--	SELECT
	--		SU.subUnitID
	--	FROM
	--		subUnits SU
	--	WHERE
	--		SU.subUnitID = @subUnitID
	--)
	--BEGIN
	--	SET @results = 'SubUnit does not exist.'
	--	SET @completed = 0
	--END
	
	----IF NOT EXISTS (
	----	SELECT
	----		U.unitID
	----	FROM
	----		units U
	----	WHERE
	----		U.unitID = @itemUnit
	----)
	----BEGIN
	----	SET @results = 'Not a valid unit.'
	----	SET @completed = 0
	----END
	
	----checks to see that there are items to allocate
	--IF NOT EXISTS(
	--	SELECT
	--		I.itemID,
	--		I.totalQuantity - SUM(SUI.StoreUnitQty) [availableUnits]
	--	FROM
	--		Items I
	--		INNER JOIN storeUnitItems SUI ON SUI.itemID = I.itemID
	--	WHERE
	--		I.itemID = @itemID
	--	GROUP BY
	--		I.itemID,
	--		I.totalQuantity
	--	HAVING
	--		(I.totalQuantity - SUM(SUI.StoreUnitQty)) >= @itemQuantity
	--)
	--BEGIN
	--	SET @results = 'Not enough items to allocate.'
	--	SET @completed = 0;
	--END
	
	----checks to see if there are items to update
	--IF NOT EXISTS(
	--	SELECT
	--		storeUnitItemID
	--	FROM
	--		storeUnits SU
	--		INNER JOIN storeUnitItems SUI ON SUI.storeUnitID = SU.storeUnitID
	--			AND SUI.itemID = @itemID
	--			AND SU.subUnitID = @subUnitID
	--)
	--BEGIN
	--	SET @results = 'Item is not allocated to that store.'
	--	SET @completed = 0
	--END
	
	--IF(@completed != 0)
	--BEGIN
	--	--UPDATE storeUnitItems
		
	
	--	UPDATE SUI
	--		SET SUI.StoreUnitQty = quantity + @itemQuantity
	--	FROM
	--		storeUnits SU
	--		INNER JOIN storeUnitItems SUI ON SUI.storeUnitID = SU.storeUnitID
	--			AND SUI.itemID = @itemID
	--			AND SU.subUnitID = @subUnitID
				
	--	SET @completed = 1
	--	SET @results = NULL
		
	--END
	
	SELECT
		@results [errorMessage]
		
	SELECT
		@completed [true/false]
	
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_allocateItem')

DROP PROCEDURE sp_allocateItem

GO

--Created by Benjamin Strickler 08/15/2012
--Modifed by Adam Venezia 10-3-2012
CREATE PROCEDURE [dbo].[sp_allocateItem] (
	@storeID INT,
	@EmergencyQty INT,
	@itemID INT--,
	--@itemQuantity INT
)

AS
BEGIN
	DECLARE @results NVARCHAR(100)
	DECLARE @completed BIT

	IF (@EmergencyQty > 0)
	BEGIN
		--DECLARE @StoreUnitItemID	INT
		
		--DECLARE List CURSOR
		--FOR
		--SELECT StoreUnitItemID FROM StoreUnitItems WHERE ItemID = @ItemID

		--OPEN List
		--FETCH NEXT FROM List
		--INTO @StoreUnitItemID

		--WHILE @@FETCH_STATUS = 0
		--BEGIN
			UPDATE Items
			SET EmergencyQty = @EmergencyQty
			WHERE ItemID = @ItemID
		--	AND StoreUnitItemID = @StoreUnitItemID
		--FETCH NEXT FROM List

		--INTO @StoreUnitItemID

		--END

		----DON"T FORGET TO CLOSE
		--CLOSE List
		----DELALLOCATE
		--DEALLOCATE List
	END
	
	
	SELECT
		@results [errorMessage]
		
	SELECT
		@completed [true/false]
	
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_adjustItemQuantity')

DROP PROCEDURE sp_adjustItemQuantity

GO

--created by Michael Meyer july 26, 2012
--modified by Travis Marceau
CREATE PROCEDURE [dbo].[sp_adjustItemQuantity] (
	@itemID		INT,
	@quantity	INT,
	@reasonID	INT,
	@userID		INT--,
	--@storeID	INT,
	--@subUnitID	INT
)
AS
BEGIN
DECLARE @results NVARCHAR(100)
	SET @results = 'Unknown Error'
--DECLARE	@storeUnitID	INT
--	SET @storeUnitID = (SELECT StoreUnitID FROM StoreUnits WHERE StoreID = @storeID AND SubUnitID = 1)
--DECLARE @storeUnitItemID INT
--	SET @storeUnitItemID = (SELECT StoreUnitItemID FROM StoreUnitItems WHERE ItemID = 1 AND StoreUnitID = 1)
DECLARE @completed BIT
	SET @completed = 0
	--IF EXISTS (
	--	SELECT
	--		itemID
	--	FROM
	--		StoreUnitItems
	--	WHERE
	--		StoreUnitID = @storeUnitID
	--)
	IF ((SELECT SUM(TransactionQty) FROM Inventory) > 0)
	BEGIN
		SET @results = NULL

		INSERT INTO ItemAdjustments (UserID, ItemID, AdjReasonID, DateAdjusted, AdjustmentQty)
			VALUES (@userID, @itemID, @reasonID, GETDATE(), @quantity*-1)
		
		----UPDATE Items
		----	SET TotalQuantity = TotalQuantity + @quantity
		--WHERE
		--	ItemID = @itemID
			
		SET @completed = 1
	END
	ELSE
	BEGIN
		SET @results = 'No stock for that item.'
		SET @completed = 0
	END
	
	SELECT
		@results [errorMessage]
		
	SELECT
		@completed
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_addVendorItem')

DROP PROCEDURE sp_addVendorItem

GO

--Created by Michael Meyer July 9, 2012
CREATE PROCEDURE [dbo].[sp_addVendorItem] (
	@itemID INT,
	@vendorID INT,
	@vendorCost FLOAT,
	@purchaseDate DATETIME,
	@quantity INT
)

AS

BEGIN

	DECLARE @results NVARCHAR(100)
		SET @results = 'Unknown Error.'
	DECLARE @completed BIT
		SET @completed = 0
	DECLARE @vendorExists BIT
		SET @vendorExists = 0
	DECLARE @itemExists BIT
		SET @itemExists = 0
	
	--Checks to see if the item exists and is active
	IF EXISTS (
		SELECT
			I.itemID
		FROM
			Items I
			INNER JOIN Statuses S ON S.statusID = I.statusID
				AND I.itemID = @itemID
				AND S.statusName = 'Active'
	)
	BEGIN
		SET @itemExists = 1
		SET @results = 'Item is inactive or does not exist.'
		SET @completed = 0
	END
	
	--Checks to see if the vendor exists and is active
	IF EXISTS (
		SELECT
			V.vendorID
		FROM
			Vendors V
			INNER JOIN Statuses S ON S.statusID = V.statusID
				AND V.vendorID = @vendorID
				AND S.statusName = 'Active'
	)
	BEGIN
		SET @vendorExists = 1
		SET @results = 'Vendor is inactive or does not exist.'
		SET @completed = 0
	END
	
	--If both vendor and item are active and exist
	IF(@vendorExists = 1 AND @itemExists = 1)
	BEGIN
		INSERT INTO VendorItems(itemID, vendorID, vendorCost, purchaseDate, quantity)
			VALUES (@itemID, @vendorID, @vendorCost, @purchaseDate, @quantity)
			
		SET @results = NULL
		SET @completed = 1
	END
	ELSE
	BEGIN
		SET @results = 'Vendor and or item do not exist or are inactive.'
		SET @completed = 0
	END
	
	SELECT
		@results [errorMessage]
	
	SELECT
		@completed	
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_addVendor')

DROP PROCEDURE sp_addVendor

GO

--created by Michael Meyer Tuesday June 5
CREATE PROCEDURE [dbo].[sp_addVendor] (
	@vendorName NVARCHAR(100),
	@vendorAddress NVARCHAR(100),
	@phoneNumber NVARCHAR(25)
)
AS
BEGIN
	
	DECLARE @statusID INT
	SET @statusID = (
		SELECT
			S.statusID
		FROM
			Statuses S
			INNER JOIN StatusTypes ST ON ST.statusTypeID = S.statusTypeID
				AND S.statusName = 'Active'
				AND ST.statusTypeName = 'Vendor'
	)


	--Checks to see if the address should be nulled
	IF(@vendorAddress = '-1')
	BEGIN
		SET @vendorAddress = NULL
	END

	--Checks to see if the phone number should be nulled
	IF(@phoneNumber = '-1')
	BEGIN
		SET @phoneNumber = NULL
	END
	
	DECLARE @results NVARCHAR(100)
	DECLARE @completed bit
		SET @completed = 0
	
	IF EXISTS ( --checks to see if the vendor exists
		SELECT
			V.vendorID
		FROM
			Vendors V
		WHERE
			V.vendorName = @vendorName
			AND V.vendorAddress = @vendorAddress
			AND V.phoneNumber = @phoneNumber
	)
	BEGIN --if the vendor already exists
		SET @results = 'Vendor already exists'
	END
	ELSE --the vendor doesn't exist and it will insert the vendor passed into the system
	BEGIN
		SET @results = NULL
		
		INSERT INTO Vendors (vendorName, vendorAddress, phoneNumber, StatusID)
		VALUES (@vendorName, @vendorAddress, @phoneNumber, @statusID)
		SET @completed = 1
	END
	
	SELECT
		@results [errorMessage]
	
	SELECT
		@completed
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_addUsers')

DROP PROCEDURE sp_addUsers

GO

--Created by Benjamin Strickler June 5 2012
CREATE PROCEDURE [dbo].[sp_addUsers] (
	@FirstName			varchar(20),
	@LastName			varchar(20),
	@UserName			nvarchar(20),
	@Password			nvarchar(200),
	@Salt				NVARCHAR(MAX),
	@PasswordHint		nvarchar(20),
	@storeID			int,
	@isAdmin			bit,
	@isGeneralManager	bit,
	@isStoreManager		bit,
	@isStoreKeeper		bit,
	@isSubUnitClerk		bit,
	@isCEO				bit,
	@isAccounting		bit,
	@isProcurement		bit

)
AS
BEGIN
	IF EXISTS (SELECT U.UserName FROM Users U WHERE U.Username = @UserName)
		BEGIN
			--this is for the error table.  Never will have any errors
			SELECT
			'The username already exists' [errors]
		END
	ELSE
	BEGIN
	
		--this declares the active user status ID
		DECLARE @statusID INT
		SET @statusID = (
			SELECT TOP 1
				S.statusID
			FROM
				Statuses S
				INNER JOIN StatusTypes ST ON ST.statusTypeID = S.statusTypeID
			WHERE
				StatusTypeName = 'User'
				AND StatusName = 'Active'
			)
		
		IF(@storeID = -1)
		BEGIN
			SET @storeID = NULL
		END
		
		INSERT INTO [dbo].[Users] (FirstName, LastName, Username, Pass, Salt, PasswordHint, StatusID, storeID)
		VALUES	(@FirstName, @LastName, @UserName, @Password, @Salt, @PasswordHint, @statusID, @storeID) 
		
		DECLARE	@UserID	int
		SET @UserID = @@Identity
		
		IF (@isAdmin = 1)
			BEGIN
				INSERT INTO [dbo].[UserRoles] ([UserID], [RoleID])
				VALUES (@UserID, 1)
			END
		IF (@isGeneralManager = 1)
			BEGIN
				INSERT INTO [dbo].[UserRoles] ([UserID], [RoleID])
				VALUES (@UserID, 2)
			END
		IF (@isStoreManager = 1)
			BEGIN
				INSERT INTO [dbo].[UserRoles] ([UserID], [RoleID])
				VALUES (@UserID, 3)
			END
		IF (@isStoreKeeper = 1)
			BEGIN
				INSERT INTO [dbo].[UserRoles] ([UserID], [RoleID])
				VALUES (@UserID, 4)
			END
		IF (@isSubUnitClerk = 1)
			BEGIN
				INSERT INTO [dbo].[UserRoles] ([UserID], [RoleID])
				VALUES (@UserID, 5)
			END
		IF (@isCEO = 1)
			BEGIN
				INSERT INTO [dbo].[UserRoles] ([UserID], [RoleID])
				VALUES (@UserID, 6)
			END
		IF (@isAccounting = 1)
			BEGIN
				INSERT INTO [dbo].[UserRoles] ([UserID], [RoleID])
				VALUES (@UserID, 7)
			END
		IF (@isProcurement = 1)
			BEGIN
				INSERT INTO [dbo].[UserRoles] ([UserID], [RoleID])
				VALUES (@UserID, 8)
			END
		SELECT
			NULL [errors]
		SELECT
			'True' [Worked]
	END
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_addSubUnit')

DROP PROCEDURE sp_addSubUnit

GO

--created by michael meyer june 25, 2012
CREATE PROCEDURE [dbo].[sp_addSubUnit] (
	@subUnitName NVARCHAR(100)
)
AS
BEGIN

DECLARE @statusID INT
SET @statusID = (
	SELECT TOP 1
		S.statusID
	FROM
		statusTypes ST
		INNER JOIN STATUSES S ON S.statusTypeID = ST.statusTypeID
	WHERE
		ST.statusTypeName = 'SubUnit'
		AND S.statusName = 'Active'
)
--error results
DECLARE @results NVARCHAR(100)
DECLARE @completed BIT

SET @completed = 0
SET @results = 'Unknown Error'

IF EXISTS (
	SELECT
		SU.subUnitID
	FROM
		SubUnits SU
	WHERE
		SU.subUnitName = @subUnitName
	)
	BEGIN --if the the item exists by that name
		SET @results = 'Sub Unit already exists.'
		
		SET @completed = 0
	END
	
	ELSE --if the item doesnt exist
	
	BEGIN
		SET @results = NULL
		
		INSERT INTO SubUnits (subUnitName, statusID)
			VALUES(@subUnitName, @statusID)
		
		SET @completed = 1
	END

	SELECT
		@results [errorMessage]
	
	SELECT
		@completed [successOrFailure]

END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_addStoreUnit')

DROP PROCEDURE sp_addStoreUnit

GO

--Created by Michael Meyer June 21 2012
CREATE PROCEDURE [dbo].[sp_addStoreUnit] (
	@storeID INT,
	@subUnitID INT
)
AS
BEGIN

	DECLARE @results NVARCHAR(100)
	SET @results = 'Unknown Error.'

	DECLARE @completed bit
	SET @completed = 0

	DECLARE @storeExists bit

	DECLARE @subUnitExists bit

	--sets status id to the active status
	DECLARE @statusID INT
	SET @statusID = (
		SELECT
			S.statusID
		FROM
			StatusTypes ST
			INNER JOIN Statuses S ON S.statusTypeID = ST.statusTypeID
		WHERE
			StatusTypeName = 'StoreUnit'
			AND StatusName = 'Active'
		)

	--Checks to see if the store exists
	IF EXISTS (
		SELECT
			S.storeID
		FROM
			stores S
		WHERE
			S.storeID = @storeID
			AND S.storeTypeID = 1 --Central store
	)
	BEGIN
		SET @storeExists = 1
	END
	ELSE
	BEGIN
		SET @storeExists = 0
		SET @results = 'Store does not exist.'
	END

	--checks to see if the sub unit exists
	IF EXISTS (
		SELECT
			SU.subUnitID
		FROM
			subUnits SU
		WHERE
			SU.subUnitID = @subUnitID
	)
	BEGIN
		SET @subUnitExists = 1
	END
	ELSE
	BEGIN
		SET @subUnitExists = 0
		SET @results = 'Sub unit does not exist.'
	END

--if the store and the sub unit exist
	IF(@storeExists = 1 AND @subUnitExists = 1)
	BEGIN
		IF EXISTS(
			SELECT
				SU.storeUnitID
			FROM
				storeUnits SU
			WHERE
				SU.subUnitID = @subUnitID
				AND SU.storeID = @storeID
			)
		BEGIN
			SET @completed = 0
			SET @results = 'Store Sub unit already exists.'
		END
		ELSE
		BEGIN
			INSERT INTO storeUnits (SubUnitID, StoreID, StatusID)
			VALUES (@subUnitID, @storeID, @statusID)
			
			DECLARE @storeUnitID INT
			SET @storeUnitID = (
				SELECT
					SU.storeUnitID
				FROM
					StoreUnits SU
				WHERE
					SU.storeID = @storeID
					AND SU.subUnitID = @subUnitID
			)

			INSERT INTO StoreUnitItems (itemID, StoreUnitQty, storeUnitID)
			SELECT
				I.itemID,
				0 [quantity],
				@storeUnitID [storeUnitID]
			FROM
				Items I
				JOIN ItemCategories IC ON I.ItemCategoryID = IC.ItemCategoryID
			WHERE
				IC.StoreID = @storeID
			
			SET @completed = 1
			
			SET @results = null
		END
	END
	
	SELECT
		@results [ErrorMessage]
	
	SELECT
		@completed
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_addStore')

DROP PROCEDURE sp_addStore

GO

--Created by Michael Meyer June 23 2012
CREATE PROCEDURE [dbo].[sp_addStore] (
	@storeName NVARCHAR(100),
	@storeTypeID INT
)
AS
BEGIN

	DECLARE @results NVARCHAR(100)
	SET @results = 'Unknown Error.'

	DECLARE @storeExists BIT
		SET @storeExists = 0
	DECLARE @storeTypeExists BIT
		SET @storeTypeExists = 0
	DECLARE @completed bit
		SET @completed = 0

	--sets status id to the active status
	DECLARE @statusID INT
	SET @statusID = (
		SELECT
			S.statusID
		FROM
			StatusTypes ST
			INNER JOIN Statuses S ON S.statusTypeID = ST.statusTypeID
		WHERE
			StatusTypeName = 'Store'
			AND StatusName = 'Active'
		)

	--Checks to see if the store exists
	IF EXISTS (
		SELECT
			S.storeID
		FROM
			stores S
		WHERE
			S.storeName = @storeName
	)
	BEGIN
		SET @results = 'Store already exists'
		
		SET @storeExists = 1
	END
	ELSE
	BEGIN
		SET @storeExists = 0
		
		SET @results = NULL
	END
	
	--Checks to see if the store type exists
	IF EXISTS (
		SELECT
			ST.storeTypeID
		FROM
			storeTypes ST
		WHERE
			ST.storeTypeID = @storeTypeID
	)
	BEGIN
		SET @results = Null
		
		SET @storeTypeExists = 1
	END
	ELSE
	BEGIN
		SET @storeTypeExists = 0
		
		SET @results = 'Store Type does not exist.'
	END
	
	--If the store does not exist and the store type does exist
	IF(@storeExists = 0 AND @storeTypeExists = 1)
	BEGIN
		INSERT INTO stores (storeTypeID, storeName, statusID)
			VALUES (@storeTypeID, @storeName, @statusID)
		SET @results = null
		SET @completed = 1
	END
	ELSE
	BEGIN
		SET @completed = 0
		
		SET @results = 'There is already a store with the specified store type.'
	END

	SELECT
		@results [ErrorMessage]
	
	SELECT
		@completed
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_addRequisition')

DROP PROCEDURE sp_addRequisition

GO

--Created by Preston Berrett June 26, 2012
--Modified by Benjamin Strickler on July 10, 2012
--Modified by Travis Marceau too
CREATE PROCEDURE [dbo].[sp_addRequisition]
	@StoreID INT,
	@RequisitionName VARCHAR(100) -- actually Requisition description
AS
BEGIN
DECLARE @results NVARCHAR(100)
	IF EXISTS (
		SELECT
			R.RequisitionID
		FROM
			Requisitions R
		WHERE
			R.StoreID = @StoreID AND
			R.StatusID = 45
	)
	BEGIN --if there aren't any creating requisitions
		SET @results = 'Requisition already exists';
		
		SELECT
			@results;
	END
	ELSE
	BEGIN
		DECLARE @StatusID INT
		IF @RequisitionName = 'Need to change Order ID'
			SET @StatusID = 46
		ELSE
			SET @StatusID = 45

		INSERT Requisitions(StoreID, OrderDate, StatusID, RequisitionName)
		VALUES (@StoreID, GETDATE(), @StatusID, @RequisitionName)
		
		DECLARE @ReqID int
		SET @ReqID = @@IDENTITY
		
		SET @results = NULL;
		
		SELECT
			@results; 
		
		SELECT
			RequisitionID, OrderDate, RequisitionName
		FROM
			Requisitions
		WHERE
			RequisitionID = @ReqID
		
	END
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_addItemToRequisition')

DROP PROCEDURE sp_addItemToRequisition

GO

CREATE PROCEDURE [dbo].[sp_addItemToRequisition]
	@RequisitionID	int,
	@ItemID			int,
	@RequestedQty	int,
	@StoreID		int
AS
BEGIN
	DECLARE @results NVARCHAR(100)
	IF EXISTS (
			SELECT 
				* 
			FROM 
				 RequisitionItems RI
				 JOIN Requisitions R ON R.RequisitionID = RI.RequisitionID
			WHERE 
				RI.RequisitionID = @RequisitionID 
				AND RI.ItemID = @ItemID
				AND R.StoreID = @StoreID)
	BEGIN
		SET @results = 'Item Already Exists In Requisition';
			
		SELECT
		@results;
	END
	ELSE
	BEGIN
		INSERT RequisitionItems (RequisitionID, ItemID, RequisitionQty, Denied, StatusID)
		VALUES (@RequisitionID, @ItemID, @RequestedQty, 0, 100)
		
		SET @results = 'Item Added to Requisition';
			
		SELECT
		@results;
	END
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_addVendorCost')

DROP PROCEDURE sp_addVendorCost

GO

CREATE PROCEDURE [dbo].[sp_addVendorCost]
	@vendorID			int,
	@itemID				int,
	@vendorCost			int,
	@markupMultiplier	float
AS
BEGIN
	DECLARE @results NVARCHAR(100)
	IF (@markupMultiplier > 1.0)
	BEGIN
		INSERT VendorCosts (VendorID, ItemID, VendorCost, MarkupMultiplier)
		VALUES (@vendorID, @itemID, @vendorCost, @markupMultiplier)
		
		SET @results = 'Item Added to VendorCosts';
			
		SELECT
		@results;
	END
	ELSE
	BEGIN
		SET @results = 'Invalid MarkupMultiplier';
			
		SELECT
		@results;
	END
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_AddItemToRequest')

DROP PROCEDURE sp_AddItemToRequest

GO

CREATE PROCEDURE [dbo].[sp_AddItemToRequest]
	@RequestID		int,
	@ItemID			int,
	@QtyRequested	int
AS
BEGIN
	DECLARE @results NVARCHAR(100)
	IF EXISTS (SELECT * FROM StoreRequestItems WHERE SatelliteStoreRequestID = @RequestID AND ItemID = @ItemID)
	BEGIN
		SET @results = 'Item Already Exists In Request';
			
		SELECT
		@results;
	END
	ELSE
	BEGIN
		INSERT StoreRequestItems (ItemID, SatelliteStoreRequestID, QtyRequested, Denied, StatusID)
		VALUES (@ItemID, @RequestID, @QtyRequested, 0, 90)
		
		SET @results = 'Item Added to Request';
			
		SELECT
		@results;
	END
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_addItem')

DROP PROCEDURE sp_addItem

GO

--created by michael meyer june 7, 2012
CREATE PROCEDURE [dbo].[sp_addItem] (
	@itemName NVARCHAR(100),
	@unitID INT,
	@vendorCost smallmoney,
	@itemCategoryID INT,
	--@totalQuantity INT,
	@emergencyQuantity INT,
	@storeID INT,
	@markupPercent FLOAT,
	@vendorID INT,
	@statusID INT,
	@minStockQty INT
)
AS
BEGIN

IF @statusID IS NULL
BEGIN
SET @statusID = (
	SELECT TOP 1
		S.statusID
	FROM
		statusTypes ST
		INNER JOIN STATUSES S ON S.statusTypeID = ST.statusTypeID
	WHERE
		ST.statusTypeName = 'Item'
		AND S.statusName = 'Active'
)
END

--error results
DECLARE @results NVARCHAR(100)
DECLARE @completed BIT

SET @completed = 0
SET @results = 'Unknown Error'

DECLARE @storeExists BIT
DECLARE @itemExists BIT
DECLARE @itemID INT
DECLARE @storeUnitID INT


SET @storeExists = 0
SET @itemExists = 1
SET @storeUnitID = -1

IF EXISTS (
	SELECT
		I.itemID
	FROM
		Items I
	WHERE
		I.itemName = @itemName
	)
	BEGIN
		SET @itemExists = 1
		SET @results = 'Item already exists'
	END
	ELSE
	BEGIN
		SET @itemExists = 0
	END
	
IF EXISTS(
	SELECT
		S.storeID
	FROM
		stores S
		INNER JOIN storeTypes ST ON ST.storeTypeID = S.storeTypeID
			AND S.storeID = 1
			AND ST.storeTypeName = 'Central Store'
	)
	BEGIN
		SET @storeExists = 1
	END
	ELSE
	BEGIN
		SET @results = 'Store does not exist'
		SET @storeExists = 0
	END
	
	IF(@storeExists = 1 AND @itemExists = 0)
	BEGIN
		SET @results = NULL
		
		--insert the item into the items table
		INSERT INTO Items (ItemName, ItemCategoryID, StatusID, EmergencyQty, minStockQty, UnitID)
			VALUES(@itemName, @itemCategoryID, @statusID, @emergencyQuantity, @minStockQty, @unitID)
		
		--gets the inserted item id that was just inserted.
		SELECT
			@itemID = I.itemID
		FROM
			Items I
			JOIN ItemCategories IC ON I.ItemCategoryID = IC.ItemCategoryID
		WHERE
			I.itemName = @itemName
			AND I.itemCategoryID = @itemCategoryID

		INSERT INTO VendorCosts (ItemID, VendorID, ModifiedDate, VendorCost, MarkupMultiplier)
			VALUES (@itemID, @vendorID, GETDATE(), @vendorCost, @markupPercent)
		
		--makes the item available for the store units
		INSERT INTO storeUnitItems (ItemID, StoreUnitQty, storeUnitID)
			SELECT
				@itemID,
				@emergencyQuantity,
				SU.storeUnitID
			FROM
				Items I
				INNER JOIN ItemCategories IC ON I.ItemCategoryID = IC.ItemCategoryID
				INNER JOIN StoreUnitItems SUI ON SUI.itemID = I.itemID
				INNER JOIN StoreUnits SU ON SU.storeID = IC.storeID
					AND SU.storeUnitID = SUI.storeUnitID
					AND SU.storeID = @storeID
					AND I.itemID = @itemID

		INSERT INTO Holds (ItemID, HoldQty, StoreRequestItemID)
			VALUES (@itemID, 0, 8)
		
		SET @completed = 1
	END

	SELECT
		@results [errorMessage]
	
	SELECT
		@completed [successOrFailure]

END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_addCategory')

DROP PROCEDURE sp_addCategory

GO

CREATE PROCEDURE [dbo].[sp_addCategory]
	@categoryName VARCHAR(50),
	@storeID INT
AS

BEGIN
	DECLARE @table0 VARCHAR(50)
	DECLARE @table1 BIT

	IF EXISTS (
			SELECT
				*
			FROM
				ItemCategories
			WHERE
				ItemCategoryName = @categoryName
			)
	BEGIN
		SET @table0 = 'Error with Category'
	END
	ELSE
		BEGIN
			INSERT INTO ItemCategories(ItemCategoryName, StoreID)
			VALUES (@categoryName, @storeID)
			
			SET @table0 = NULL
			
			SET @table1 = 1
		END
	
	SELECT
		@table0
		
	SELECT
		@table1

END

GO

-- SP_ADD VENDOR
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_addVendor')

DROP PROCEDURE sp_addVendor

GO

CREATE PROCEDURE [dbo].[sp_addVendor]
	@vendorName		VARCHAR(50),
	@vendorAddress	VARCHAR(50),
	@phone			VARCHAR(25),
	@statusID		INT

AS

BEGIN
	DECLARE @table0 VARCHAR(50)
	DECLARE @table1 BIT

	IF EXISTS (
			SELECT
				*
			FROM
				Vendors
			WHERE
				VendorName = @vendorName
			)
	BEGIN
		SET @table0 = 'Error with Vendor'
	END
	ELSE
		BEGIN
			INSERT INTO Vendors(VendorName, VendorAddress, PhoneNumber, StatusID)
			VALUES (@vendorName, @vendorAddress, @phone, @statusID)
			
			SET @table0 = NULL
			
			SET @table1 = 1
		END
	
	SELECT
		@table0
		
	SELECT
		@table1

END

GO


IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_acceptRequisition')

DROP PROCEDURE sp_acceptRequisition

GO

--Created by Preston Berrett June 26, 2012
CREATE PROCEDURE [dbo].[sp_acceptRequisition]
	@requisitionID INT,
	@userID INT,
	@isGM BIT,
	@isCEO BIT
AS
BEGIN
DECLARE @results NVARCHAR(100)
	IF EXISTS (
		SELECT
			R.RequisitionID
		FROM
			Requisitions R
		WHERE
			R.RequisitionID = @requisitionID
	)
	BEGIN
		IF(@isGM = 1)
		BEGIN
			UPDATE Requisitions
				SET StatusID = 42,	/*	Approved by GM	*/
					GMSig = @userID
			WHERE
				RequisitionID = @requisitionID
		END
		ELSE IF(@isCEO = 1)	
		BEGIN
			UPDATE Requisitions
				SET StatusID = 43,	/*	Approved by CEO	*/
					CEOSig = @userID
			WHERE
				RequisitionID = @requisitionID
		END
	END
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_acceptRejectRequisitionItem')

DROP PROCEDURE sp_acceptRejectRequisitionItem

GO

--Created by Preston Berrett June 26, 2012
CREATE PROCEDURE [dbo].[sp_acceptRejectRequisitionItem]
	@RequisitionID INT,
	@ItemID INT,
	@IsDenied BIT
AS
BEGIN
DECLARE @results NVARCHAR(100)
	IF NOT EXISTS (
		SELECT
			RI.RequisitionItemID
		FROM
			RequisitionItems RI
		WHERE
			RI.RequisitionID = @RequisitionID
			AND RI.ItemID = @ItemID
	)
	BEGIN --if there aren't any pending requisitions
		SET @results = 'No Requisitions';
		
		SELECT
			@results;
	END
	ELSE
	BEGIN
		
		UPDATE RequisitionItems
		SET	Denied = @IsDenied
		WHERE 
			RequisitionID = @RequisitionID
			AND ItemID = @ItemID
			
		UPDATE RequisitionItems
		SET	StatusID = 41
		WHERE 
			RequisitionID = @RequisitionID
			AND ItemID = @ItemID
		
		IF NOT EXISTS(SELECT * FROM RequisitionItems WHERE Denied = 0 AND RequisitionID = @RequisitionID)
		BEGIN
			UPDATE Requisitions SET StatusID = 41 WHERE RequisitionID = @RequisitionID
		END
		
		SET @results = NULL;
		
		SELECT
			@results; 
			
		SELECT
			RI.RequisitionItemID
		FROM
			RequisitionItems RI
		WHERE
			RI.RequisitionID = @RequisitionID
			AND RI.ItemID = @ItemID;
	END
END

GO


IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_denyWithComments')

DROP PROCEDURE sp_denyWithComments

GO

--Created by Preston Berrett June 26, 2012
CREATE PROCEDURE [dbo].[sp_denyWithComments]
	@RequisitionID INT,
	@Comments NVARCHAR(250)
AS
BEGIN
DECLARE @results NVARCHAR(100)
	IF NOT EXISTS (
		SELECT
			RI.RequisitionItemID
		FROM
			RequisitionItems RI
		WHERE
			RI.RequisitionID = @RequisitionID
	)
	BEGIN --if there aren't any pending requisitions
		SET @results = 'No Requisitions';		
	END
	ELSE
	BEGIN
		
		UPDATE Requisitions SET DenialComments = @Comments WHERE RequisitionID = @RequisitionID
		
	END
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'getVendorByID')

DROP PROCEDURE getVendorByID

GO

--Created by Michael Meyer July 11 2012
CREATE PROCEDURE [dbo].[getVendorByID] (
	@vendorID INT
)
AS
BEGIN
DECLARE @results NVARCHAR(100)
	--checks to see if there is a vendor by the ID passed in
	IF EXISTS (
		SELECT
			V.vendorID
		FROM
			Vendors V
		WHERE
			V.vendorID = @vendorID
	)
	BEGIN
		SET @results = NULL
	END
	
	ELSE
	
	BEGIN
		SET @results = 'No vendor by that ID'
	END
	
	SELECT
		@results
		
	SELECT
		V.vendorID,
		V.vendorName,
		V.vendorAddress,
		V.phoneNumber,
		V.statusID
	FROM
		Vendors V
	WHERE
		V.vendorID = @vendorID

END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'getMonthDates')

DROP PROCEDURE getMonthDates

GO

--Created by Michael Meyer July 31, 2012
CREATE PROCEDURE [dbo].[getMonthDates] (
	@year INT,
	@month INT
)
AS
BEGIN
	SELECT
		MIN([dateName]) [startMonth],
		MAX([dateName]) [endMonth]
	FROM
		calendarBasic CB
	WHERE
		CB.calendarYear = @year
		AND CB.fiscalMonthOfYear = @month
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_markRequestStatusByStatusID')

DROP PROCEDURE sp_markRequestStatusByStatusID

GO

--Created By Bryan Nixon 8-17-2012
CREATE PROCEDURE [dbo].[sp_markRequestStatusByStatusID] (
	@requestID		int,
	@statusID		int
)
AS
BEGIN
DECLARE @results NVARCHAR(100)
	
	UPDATE SatelliteStoreRequests
	SET StatusID = @statusID
	WHERE
		SatelliteStoreRequestID = @requestID
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_ChangeRequestDescription')

DROP PROCEDURE sp_ChangeRequestDescription

GO

--Created By Bryan Nixon 8-17-2012
CREATE PROCEDURE [dbo].[sp_ChangeRequestDescription] (
	@requestID		int,
	@description	varchar(100)
)
AS
BEGIN
DECLARE @results NVARCHAR(100)
	
	UPDATE SatelliteStoreRequests
	SET RequestDescription = @description
	WHERE
		SatelliteStoreRequestID = @requestID
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_UpdateRequestStatus')

DROP PROCEDURE sp_UpdateRequestStatus

GO

--Created By Bryan Nixon 8-17-2012
CREATE PROCEDURE [dbo].[sp_UpdateRequestStatus]
	@RequestID				int
AS
BEGIN
DECLARE @newStatus int
DECLARE @Status int
DECLARE db_cursor CURSOR FOR  
	SELECT DISTINCT [StatusID] 
	FROM [StoreRequestItems] 
	WHERE SatelliteStoreRequestID = @RequestID  

SET @newStatus = 82 -- IF there are no Items Pending then Request Status is "Ready for Pickup" 

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @Status   

	WHILE @@FETCH_STATUS = 0   
	BEGIN   
		IF(@Status = 90) -- If Item Status is Pending
			SET @newstatus = 112
		IF(@Status = 112)
			SET @newStatus = 112 -- Then Request Status is Partially Approved

		FETCH NEXT FROM db_cursor INTO @Status   
	END   

CLOSE db_cursor   
DEALLOCATE db_cursor
	BEGIN
			
		EXEC sp_markRequestStatusByStatusID @RequestID, @newStatus
		
	END
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_DeleteRequest')

DROP PROCEDURE sp_DeleteRequest

GO

--Created By Bryan Nixon 8-17-2012
CREATE PROCEDURE [dbo].[sp_DeleteRequest]
	@RequestID	int
AS
BEGIN
	DELETE FROM [StoreRequestItems] -- Deletes Items from the Request
    WHERE SatelliteStoreRequestID = @RequestID
    
    DELETE FROM [SatelliteStoreRequests] -- Deletes the Request
    WHERE SatelliteStoreRequestID = @RequestID
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_MarkRequestItemPartialFill')

DROP PROCEDURE sp_MarkRequestItemPartialFill

GO

--Create by Bryan Nixon 8/17/2012
CREATE PROCEDURE [dbo].[sp_MarkRequestItemPartialFill] (
	@RequestID		int,
	@ItemID			int
)
AS

BEGIN
	UPDATE StoreRequestItems
	SET StatusID = 113 --Partially Filled Item Status
	WHERE SatelliteStoreRequestID = @RequestID
	AND ItemID = @ItemID	
END

GO

--Created by Adam Venezia 10-21-12
--Takes in a requisitionID, ItemID, and that item's Quantity from a requisition.
--Update the quantity field for the given ItemID
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_modifyRequisition')

DROP PROCEDURE sp_modifyRequisition

GO

CREATE PROCEDURE [dbo].[sp_modifyRequisition]
	@requisitionID INT,
	@storeID INT,
	@itemID INT,
	@qty INT
AS
BEGIN
DECLARE @results NVARCHAR(100)
	IF NOT EXISTS (
		SELECT
			RI.RequisitionItemID
		FROM
			RequisitionItems RI
		WHERE
			RI.RequisitionID = @requisitionID
	)
	BEGIN --if there aren't any pending requisitions
		SET @results = 'No Requisitions';
		
		SELECT
			@results;
	END
	ELSE
	BEGIN
	UPDATE RequisitionItems
		SET RequisitionQty = @qty
		WHERE 
			RequisitionID = @RequisitionID
			AND
			ItemID = @itemID
		
		SET @results = NULL;
		
		SELECT
			@results; 
			
		SELECT
			@requisitionID;
	END
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_DeleteRequisition')

DROP PROCEDURE sp_DeleteRequisition

GO

--Created by Adam Venezia 10-16-12
--Providing a RequisitionID, we can delete a requisition, provided all RequisitionItems related
--to the given RequisitionID have been deleted previously. @@ROWCOUNT can very that a row was deleted.
CREATE PROCEDURE [dbo].[sp_DeleteRequisition]
	@RequisitionID INT
AS
DECLARE @results NVARCHAR(100)
BEGIN
IF NOT EXISTS (
		SELECT
			RI.RequisitionItemID
		FROM
			RequisitionItems RI
		WHERE
			RI.RequisitionID = @RequisitionID
	)
	BEGIN -- if there is no requisition with that item
		SET @results = 'No Requisitions';
		
		SELECT
			@results;
	END
	ELSE
	SET @results = NULL;
		
		SELECT
			@results;

	DELETE FROM [Requisitions]
		WHERE RequisitionID = @RequisitionID

	--Built in variable @@ROWCOUNT keeps track of how many rows were affected by the last query.
	SELECT @@ROWCOUNT;
	
	END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_ChangeRequisitionDescription')

DROP PROCEDURE sp_ChangeRequisitionDescription

GO

--Created by Adam Venezia 10-16-12
--Providing a RequisitionID, we can update the Description, or Requisitions.RequisitionName
--Returns results and the the RequisitionName after it has been changed.
CREATE PROCEDURE [dbo].[sp_ChangeRequisitionDescription]
	@RequisitionID INT,
	@Description NVARCHAR(100)
AS
DECLARE @results NVARCHAR(100)
BEGIN
IF NOT EXISTS (
		SELECT
			RI.RequisitionItemID
		FROM
			RequisitionItems RI
		WHERE
			RI.RequisitionID = @RequisitionID
	)
	BEGIN -- if there is no requisition with that item
		SET @results = 'No Requisitions';
		
		SELECT
			@results;
	END
	ELSE

	SET @results = NULL;
		
		SELECT
			@results;

	UPDATE [Requisitions]
	SET RequisitionName = @Description
		WHERE RequisitionID = @RequisitionID;

	SELECT RequisitionName
		FROM [Requisitions]
			WHERE RequisitionID = @RequisitionID;

END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'sp_getRequisitionByRequisitionID')

DROP PROCEDURE sp_getRequisitionByRequisitionID

GO

--Created by Adam Venezia 10-21-12
--Get a specific requistion by the passed in ID to help out with the CEO signoff print off.
CREATE PROCEDURE [dbo].[sp_getRequisitionByRequisitionID]
	@RequisitionID INT
AS
BEGIN
DECLARE @results NVARCHAR(100)
	IF NOT EXISTS (
		SELECT
			RequisitionID,
			RequisitionName,
			OrderDate,
			DenialComments
		FROM Requisitions
		WHERE
			RequisitionID = @RequisitionID
	)
	BEGIN --if there aren't any pending requisitions
		SET @results = 'No Requisitions';
		
		SELECT
			@results;
	END
	ELSE
	BEGIN
		
		SET @results = NULL;
		
		SELECT
			@results;
			
		SELECT
			RequisitionID,
			RequisitionName,
			OrderDate,
			DenialComments
		FROM Requisitions
		WHERE
			RequisitionID = @RequisitionID
	END
END


GO